Read an ID by name from a web address

Turn a link with ?providerId=000123&view=full into 000123, zeros kept.

What you'll make

In Excel: example.test/p?providerId=000123&view=full + providerId becomes 000123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1TEXTAFTER(A8,"?")Keeps the link's text after the ?."providerId=000123…"
2"&"&B8&"="The name to find, with & before and = after."&providerId="
3TEXTAFTER( … )Keeps the text after that name."000123&view=full"
4TEXTBEFORE( … ,"&")Keeps the text up to the next &."000123"
5IFERROR( … )Shows Review parameter when the name isn't there."000123"

Each bracket wraps the one before it, so Excel works from the inside out. The & pieces make the first and last values work too.

=IFERROR(TEXTBEFORE(TEXTAFTER("&"&TEXTAFTER(A8,"?"),"&"&B8&"=")&"&","&"),"Review parameter")

How to use it

Yellow bar at the top? Click Enable Editing.

  1. See it work. The Example tab shows 20 made-up examples with the formula already in.
  2. Use your own links. Paste each web address and the name to read into the green columns of the Template tab. The IDs fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Type the name exactly, capitals included. Links with %20 codes or a # part may need a look.

Microsoft's help: TEXTAFTER function