Turn a code like \u00e9 back into a letter

Turn Jos\u00e9 Williams from an export into José Williams.

What you'll make

In Excel: Jos\u00e9 Williams becomes José Williams.

The formula

The formula, with a numbered bracket under each part.
NumberPartWhat it doesResult so far
1ISERROR(FIND("\u",A8))Checks if the name has no \u code.No, it has one
2before,TEXTBEFORE(A8,"\u"),tail,…Splits the name around the \u."Jos" and "00e9 Williams"
3cp,HEX2DEC(LEFT(tail,4))Reads the four characters after \u as a letter number.233
4IFERROR(before&UNICHAR(cp)&…)Puts that letter between the two pieces."José Williams"
5IF( … )Keeps the name as typed if part 1 says yes, else uses part 4."José Williams"

Parts 2 to 4 sit inside LET, which names each piece so it can be reused. The names here are made-up.

=IF(ISERROR(FIND("\u",A8)),A8,LET(before,TEXTBEFORE(A8,"\u"),tail,TEXTAFTER(A8,"\u"),cp,HEX2DEC(LEFT(tail,4)),IFERROR(before&UNICHAR(cp)&MID(tail,5,LEN(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 names. Paste exported names into the green column of the Template tab. The decoded names fill in by themselves.
  3. Move them into your letter. Copy the results. Then use Paste Special and choose Values.

Before you send

This fixes one \u code per name. If a name has two or more, decode it in the program that exported it.

Microsoft's help: HEX2DEC function