Unwrap a CSV field with doubled quotes

Turn "Reed, Ava" from a CSV file into Reed, Ava, with "" back to ".

What you'll make

In Excel: "Ava ""Avie"" Reed" becomes Ava "Avie" Reed.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1AND(LEFT(A8,1)=CHAR(34),RIGHT(…),LEN(…)>=2)Checks the field starts and ends with a " mark.TRUE
2MID(A8,2,LEN(A8)-2)Takes off the first and last " marks."Ava ""Avie"" Reed"
3SUBSTITUTE( … ,CHAR(34)&CHAR(34),CHAR(34))Turns each "" pair back into one " mark."Ava "Avie" Reed"
4"Review quoted field"Shows this when the outer " marks are missing.Not used for row 8
5IF( … )Uses parts 2 and 3 when part 1 is TRUE, otherwise part 4."Ava "Avie" Reed"

CHAR(34) is the " mark. Part 2 runs first, then part 3 fixes what it gives. Commas and line breaks inside stay.

=IF(AND(LEFT(A8,1)=CHAR(34),RIGHT(A8,1)=CHAR(34),LEN(A8)>=2),SUBSTITUTE(MID(A8,2,LEN(A8)-2),CHAR(34)&CHAR(34),CHAR(34)),"Review quoted field")

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 fields. Paste one quoted field per row into the green column of the Template tab. The field text fills in by itself.
  3. 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, with no spaces outside its quote marks.

Microsoft's help: SUBSTITUTE function