Using the Excel CLEAN Function
SummaryThe Excel CLEAN function is used to remove all non-printable characters from text. This function is useful if you are importing text from other applications that may contain characters that will not print with your operating system. For example, some low-level computer code may appear at the beginning and end of data files that cannot be printed. CLEAN will remove this low-level code.
It should be noted that CLEAN may not remove all non-printing characters. The function was designed to address the first 32 non-printing characters in the 7-bit ASCII code (0 through 31). The Unicode character set has additional non-printing characters that CLEAN will not be able to remove. These include values 127, 129, 141, 143, 144, and 157.
To remove the ASCII space character (32), use the TRIM function.
Required. The data from which you wish to remove non-printing characters.
Usage NotesThe CLEAN function receives a text string and "cleans" it of any non-printing characters and line breaks.
The function removes the first 32 non-printing characters in the 7-bit ASCII code. For assistance with additional non-printing characters found in Unicode, and other helpful tips for cleaning your data, check out our article on 10 ways to clean your data in Excel.