Decode at signs and plus signs in emails
Turn %40 back into @ and %2B back into +.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The email, with %40 and %2B codes. | "ava%2Bteam%40example.com" |
| 2 | SUBSTITUTE( … "%40" … ) | Turns every %40 into @. | "ava%2Bteam@example.com" |
| 3 | SUBSTITUTE( … "%2B" … ) | Turns every %2B into +. | "ava+team@example.com" |
| 4 | SUBSTITUTE( … "%2b" … ) | Does the same for a small-letter %2b. | "ava+team@example.com" |
Each bracket wraps the one inside it, so Excel works from the inside out. The addresses here are made-up.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"%40","@"),"%2B","+"),"%2b","+")How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own list. Paste coded emails into the green column of the Template tab. The emails fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this only when the list codes @ and + and nothing else. Other % codes stay as they are.
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Get an email address from a mailto link
mailto:[email protected]?subject=Hello → [email protected]
Get the template →
Read an address from Name <address>
Ava Reed <[email protected]> → [email protected]
Get the template →
Remove spaces from an email address
ava @ example.com → [email protected]
Get the template →
Check each email went to the right address, once
Your list of 2 → 2 sent copies, each to the right address
Take this route →