Read an ID at the end of a web address

Turn a link ending in /000123?view=full into 000123, zeros kept.

What you'll make

In Excel: https://example.test/provider/000123?view=full becomes 000123.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1A8The web address."…/000123?view=full"
2TEXTAFTER( … ,"/",-1)Keeps the text after the last slash."000123?view=full"
3&"?"Adds a ? at the end, so there is always one."000123?view=full?"
4TEXTBEFORE( … ,"?")Keeps the text before the first ?."000123"

Each bracket wraps the one before it, so Excel works from the inside out. The -1 means the last slash.

=TEXTBEFORE(TEXTAFTER(A8,"/",-1)&"?","?")

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 web addresses into the green column 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

Use this only for links that end in the ID. A slash at the end or a # part gives the wrong ID.

Microsoft's help: TEXTAFTER function