How do I remove invisible characters in Excel?

Use SUBSTITUTE with UNICHAR, because Excel's CLEAN and TRIM miss the characters that usually cause the trouble. CLEAN only removes non-printing control characters (codes 0 to 31), and TRIM only removes extra regular spaces. Neither touches non-breaking spaces or zero-width spaces, which often come along with text pasted from web pages, ChatGPT, Copilot or other AI tools. Remove AI Tells sees these characters in pasted AI text every day.

The formula

For text in A1:

=TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,UNICHAR(160)," "),UNICHAR(8239)," "),UNICHAR(8203),"")))

• UNICHAR(160) non-breaking space, swapped for a normal space
• UNICHAR(8239) narrow no-break space, swapped for a normal space
• UNICHAR(8203) zero-width space, removed

Add another SUBSTITUTE for UNICHAR(8288) (word joiner) or UNICHAR(65279) (zero-width no-break space) if your data has them. UNICHAR works in Excel 2013 and later.

Signs you have hidden characters

• XLOOKUP or VLOOKUP returns #N/A for a value you can see in the list.
• Numbers are stored as text and won't add up.
• =LEN(A1) returns more characters than you can count.

How to identify the character

=UNICODE(MID(A1,5,1)) returns the code of the fifth character. Change the 5 to walk through the cell until you find a code like 160 or 8203.

Find and Replace option

For non-breaking spaces on Windows, press Ctrl+H, click in Find what, hold Alt and type 0160 on the number pad, put a normal space in Replace with, and choose Replace All.

The faster option for text

For paragraphs rather than data columns, paste the text into the free Remove AI Tells scanner. It removes invisible characters, turns special spaces into normal ones and shows you how many it found.