Remove a CDATA wrapper, keep &

Turn <![CDATA[Ava &amp; Noah Reed]]> into Ava &amp; Noah Reed.

What you'll make

In Excel: <![CDATA[Ava &amp; Noah Reed]]> becomes Ava &amp; Noah Reed.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1EXACT(LEFT(A8,9),"<![CDATA[")Checks the field starts with <![CDATA[ in capitals.Yes
2RIGHT(A8,3)="]]>",LEN(A8)>=12Checks it ends with ]]> and is long enough.Yes
3MID(A8,10,LEN(A8)-12)Keeps the text between the two markers."Ava &amp; Noah Reed"
4"Review CDATA wrapper"What the cell says if a check fails.Not used for row 8
5IF(AND( … ))Uses part 3 when parts 1 and 2 pass, else part 4."Ava &amp; Noah Reed"

Read it left to right. AND needs every check in parts 1 and 2 to pass. Codes inside, like &amp;, stay as typed.

=IF(AND(EXACT(LEFT(A8,9),"<![CDATA["),RIGHT(A8,3)="]]>",LEN(A8)>=12),MID(A8,10,LEN(A8)-12),"Review CDATA wrapper")

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 field per row into the green column of the Template tab. The content 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. A field with two wrappers in a row needs a review.

Microsoft's help: EXACT function