Turn codes like < back into symbols
Turn < > " ' and & from web or XML text into < > " ' and &.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | SUBSTITUTE(A8,"<","<") | Turns each < into a < sign. | "Ava "Avie…" |
| 2 | SUBSTITUTE( … ">" … ) | Turns each > into a > sign. | "Ava "Avie…" |
| 3 | SUBSTITUTE( … """ … ) | Turns each " into a " mark. | "Ava "Avie" Reed" |
| 4 | SUBSTITUTE( … "'" … ) | Turns each ' into a ' mark. | "Ava "Avie" Reed" |
| 5 | SUBSTITUTE( … "&" … ) | Turns each & into an & last of all. | "Ava "Avie" Reed" |
Each bracket wraps the one above it, so Excel works from the inside out. & goes last so it can't make a new code.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"<","<"),">",">"),""",CHAR(34)),"'","'"),"&","&")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 fields. Paste one field per row into the green column of the Template tab. The decoded text fills in by itself.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this on one field you have already split out. Other codes, like &, stay as they are.
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Turn codes like é into characters
Ava Reed & Noah Park → Ava Reed & Noah Park
Get the template →
Turn a code like \u00e9 back into a letter
Jos\u00e9 Williams → José Williams
Get the template →
Decode plus-sign spaces in form text
Ava+Reed+%2B+Noah+Park → Ava Reed + Noah Park
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →