CHAR Function in Excel (and CODE): Find Odd Characters

The CHAR function in Excel turns a number code into a character, and its partner CODE turns a character back into its number. Together they solve a frustrating problem: text that looks fine but won't match, sort or trim because of a hidden character. This guide is for anyone cleaning imported data, and it shows how to identify the odd character with CODE and remove it with SUBSTITUTE and CHAR.

What does the CHAR function do?#

CHAR returns the character that belongs to a number from 1 to 255. Every character a computer stores has a code: A is 65, a space is 32, a line break is 10.

=CHAR(number)

You rarely need CHAR to type normal letters. Its real use is to write characters you can't easily type into a formula, such as a line break, a tab or a non-breaking space.

CODE does the reverse. =CODE("A") returns 65, and =CODE(A2) returns the code of the first character in A2.

Useful CHAR codes to know#

A handful of codes cause almost every "invisible character" problem. Learn these and you can fix most messy imports:

Codes 1 to 127 are the same on every computer. Codes 128 to 255 come from the computer's character set, so a few of them can give a different character on a Mac than on Windows. For full Unicode characters, use UNICHAR and UNICODE (Excel 2013 and later). For example, =UNICHAR(8203) is a zero-width space, which is invisible and has no width at all.

How to use CODE to find the odd character#

When two values look identical but don't match, check their length and then their codes. Start with LEN:

=LEN(A2)

If A2 shows "SKU 4471" (8 characters) but LEN returns 9, something hidden is there. Now check the characters one at a time:

• =CODE(RIGHT(A2,1)) gives the code of the last character. A result of 160 means a trailing non-breaking space. A result of 32 means a normal trailing space.
• =CODE(LEFT(A2,1)) gives the code of the first character.
• =CODE(MID(A2,4,1)) gives the code of the 4th character, the gap between SKU and 4471. A result of 160 instead of 32 explains why a lookup for "SKU 4471" fails.

In Microsoft 365 you can list every code at once:

=CODE(MID(A2,SEQUENCE(LEN(A2)),1))

SEQUENCE makes the numbers 1 to LEN(A2), so MID pulls out each character and CODE spills one code per row. Scan the list for anything that isn't a letter, digit or 32.

This matters because Excel compares text character by character. To XLOOKUP, COUNTIF and EXACT, a normal space (32) and a non-breaking space (160) are different characters, even though they look the same on screen. One hidden code is enough to turn a match into #N/A or a count of 0, which is why checking codes is often the fastest way to explain a lookup that "should" work.

Count hidden characters with LEN and SUBSTITUTE#

To count how many times a character appears, remove it and see how much shorter the text gets:

=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(160),""))

With "SKU 4471 " in A2, where both the gap and the trailing character are non-breaking spaces, this returns 2. SUBSTITUTE replaces every CHAR(160) with nothing, and the difference in length is the count.

The same pattern counts line breaks. Swap in CHAR(10) and add 1 to get the number of lines in a cell:

=LEN(A2)-LEN(SUBSTITUTE(A2,CHAR(10),""))+1

Remove odd characters with SUBSTITUTE, TRIM and CLEAN#

Once you know the code, SUBSTITUTE with CHAR removes or replaces it. The SUBSTITUTE function guide covers its arguments.

The first one is the fix for the most common complaint: TRIM not working. TRIM only removes code 32, so it leaves CHAR(160) alone. Turning it into a normal space first lets TRIM finish the job. The TRIM function guide explains exactly what TRIM removes.

CLEAN removes codes 0 to 31, which includes tabs and line breaks, but not 160. The comparison of TRIM vs CLEAN shows when to use each. To delete other specific characters, see how to remove characters in Excel, and for cells full of breaks, the guide to removing line breaks.

You can also use Find and Replace (Windows: Ctrl+H; Mac: Control+H). In the Find box on Windows, hold Alt and type 0160 on the number pad to enter a non-breaking space, or press Ctrl+J to enter a line break. A formula is easier to check and repeat, though.

Add line breaks and quotes with CHAR#

CHAR is also useful for building text. To put an address on two lines in one cell:

=A2&CHAR(10)&B2

Turn on Wrap Text (Home > Wrap Text) or the break won't show. The guide to line breaks in a formula covers this in more detail.

To put double quotes around a name, use CHAR(34), which is easier to read than doubled quotes:

="Customer: "&CHAR(34)&A2&CHAR(34)

With Priya in A2, this returns Customer: "Priya".

Practise it#

Hidden characters are a classic data-cleaning test question because the cell looks correct. Try the exercise on this page: the cell looks normal, but the count shows otherwise. Then practise cleaning text in a live spreadsheet: you'll use TRIM, CLEAN and SUBSTITUTE on messy imports, and every answer is checked instantly.