Get an email address from a mailto link

Turn mailto:ava@example.com?subject=Hello into ava@example.com.

What you'll make

In Excel: mailto:ava@example.com?subject=Hello becomes ava@example.com.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1LEFT(LOWER(TRIM(A8)),7)="mailto:"Checks if the cell starts with mailto:, in any capitals.TRUE
2MID(TRIM(A8),8,999)&"?"Drops mailto: and adds a ? at the end."ava@example.com?…"
3TEXTBEFORE( … ,"?")Keeps the text before the first ?."ava@example.com"
4TRIM(A8)A plain address, with extra spaces removed.Not used for row 8
5IF( … )Gives part 3 for a mailto link, otherwise part 4."ava@example.com"

Parts 2 and 3 work from the inside out. The added ? covers links with no ?.

=IF(LEFT(LOWER(TRIM(A8)),7)="mailto:",TEXTBEFORE(MID(TRIM(A8),8,999)&"?","?"),TRIM(A8))

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 mailto links or addresses into the green column of the Template tab. The addresses fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Check links with several addresses or a name in them. Only one plain address comes out right.

Microsoft's help: TEXTBEFORE function