CLEAN formula to clear non-printing characters in Excel

In imported data from external sources, such as text files or data connections through the internet, non-printable characters may appear.



How to Remove Non-Printable Characters in Excel?

Instead of removing them manually, you can use the special Excel function CHAR to eliminate all non-printable characters from text:

CLEAN Non-Printable Characters

download file Download

As a result, product names in the price list contain clean text without unnecessary spaces or other debris.

The CHAR function removes incorrect characters that cannot be printed from the specified text.

It is advisable to use this function as an argument for the SUBSTITUTE function. With this formula, we simultaneously clean the text from non-printable characters and unnecessary spaces or indentation.