Turn codes like é into characters

Turn José Muñoz into José Muñoz. Works for one code per cell.

What you'll make

In Excel: Ava Reed & Noah Park becomes Ava Reed & Noah Park.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1ISERROR(FIND("&#",A8)),A8Keeps the cell as it is if it has no &# code.Not used for row 8
2before,…,tail,…,n,TEXTBEFORE(tail,";")Splits the text around the code, called before, tail and n."Ava Reed " and "38"
3cp,IF(LEFT(n,1)="x",HEX2DEC(…),VALUE(n))Reads the code number. A code with x counts in 16s.38
4IFERROR(before&UNICHAR(cp)&…,"Review character code")Puts the real character back between the two sides."Ava Reed & Noah Park"
5IF( … )Uses part 1 when there is no code, otherwise parts 2 to 4."Ava Reed & Noah Park"

Read it left to right. & and & are the same code.

=IF(ISERROR(FIND("&#",A8)),A8,LET(before,TEXTBEFORE(A8,"&#"),tail,TEXTAFTER(A8,"&#"),n,TEXTBEFORE(tail,";"),cp,IF(LEFT(n,1)="x",HEX2DEC(MID(n,2,6)),VALUE(n)),IFERROR(before&UNICHAR(cp)&TEXTAFTER(tail,";"),"Review character code")))

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 list. Paste text with codes like é into the green column of the Template tab. The plain text fills in by itself.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

Each cell can hold one code. A second code in the same cell stays as it is.

Microsoft's help: UNICHAR function