How to Remove Non-Printing Characters in Excel

CLEAN strips invisible tabs, line breaks and control characters from text exported by legacy systems so lookups and matches work again.

You will learn to

• Remove control characters with CLEAN
• Measure how many hidden characters a cell contains
• Combine CLEAN and TRIM for a full cleanup

How it works

Text exported from ERPs, mainframes or PDFs often contains characters you cannot see: tabs, line feeds, carriage returns and other control codes (ASCII 0–31). They make two values that look identical fail to match in XLOOKUP or COUNTIF. =CLEAN(A2) removes all of them.

CLEAN does not remove ordinary spaces, and it does not remove the non-breaking space (character 160) that often comes from web pages. That is why the standard cleanup formula is =TRIM(CLEAN(A2)), with an extra SUBSTITUTE(A2,CHAR(160)," ") when you paste from the web. Compare LEN before and after to see how much was removed.

=TRIM(CLEAN(A2))