Turn codes like \F\ back into symbols
Turn Reed\F\Ava into Reed|Ava in one HL7 field.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | SUBSTITUTE(A8,"\F\","|") | Turns each \F\ into a | bar. | "Reed|Ava" |
| 2 | SUBSTITUTE( … ,"\S\","^") | Turns each \S\ into a ^ mark. | "Reed|Ava" |
| 3 | SUBSTITUTE( … ,"\R\","~") | Turns each \R\ into a ~ mark. | "Reed|Ava" |
| 4 | SUBSTITUTE( … ,"\T\","&") | Turns each \T\ into an & sign. | "Reed|Ava" |
| 5 | SUBSTITUTE( … ,"\E\","\") | Turns each \E\ into a \ last of all. | "Reed|Ava" |
Each bracket wraps the one above it, so Excel works from the inside out. \E\ goes last so it can't make a new code.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A8,"\F\","|"),"\S\","^"),"\R\","~"),"\T\","&"),"\E\","\")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, never on a whole message. Other codes, like \H\, stay as they are.
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Turn _x0020_ into spaces in headers
First_x0020_Name → First Name
Get the template →
Join semicolon choices with commas
Email; Phone; Text message; → Email, Phone, Text message
Get the template →
Get the language from a tag like en-US
en-US → en
Get the template →
Merge a few test letters before the whole mailing
Whole mailing → records 2 to 4 only
Take this route →