Fix doubled quotes in an exported name
Turn Ava ""Avie"" Reed from an export into Ava "Avie" Reed for letters and labels.
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | A8 | The exported name, with each quote mark typed twice. | "Ava ""Avie"" Reed" |
| 2 | CHAR(34)&CHAR(34) | Two straight quote marks side by side. | "" |
| 3 | CHAR(34) | What goes in their place: one quote mark. | " |
| 4 | SUBSTITUTE( … ) | Turns every doubled quote mark into one. | "Ava "Avie" Reed" |
CHAR(34) is a straight double quote mark. Apostrophes stay exactly as they are.
=SUBSTITUTE(A8,CHAR(34)&CHAR(34),CHAR(34))How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up names with the formula already in.
- Use your own names. Paste names into the green column of the Template tab. Clean names fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Use this on one name field, not a whole exported line. Quote marks around the whole name stay.
Route: Get names ready for the letter
Microsoft's help: SUBSTITUTE function
OTHER ROUTES YOU MIGHT LIKE

Decode an HTML ampersand in a name
Ava Reed & Noah Park → Ava Reed & Noah Park
Get the template →
Replace HTML br tags in names
Ava<br>Reed → Ava Reed
Get the template →
Fix backslash quotes in imported names
Ava \"Avie\" Reed → Ava "Avie" Reed
Get the template →
Fix "Dear ," when a first name is blank
(blank) → Dear colleague,
Take this route →