The CLEAN function removes all non-printable characters from a text string. Characters like line breaks, tabs, and other characters that cannot be printed or displayed in the normal way.
Contents:
Syntax
= CLEAN(text)text - the text string to be scanned and have any non-printable characters from it removed
Explanation
The CLEAN function is part of the "Text" group of functions within Excel.
When using the CLEAN function, Excel looks for any non-printable characters in the text and removes them, leaving only printable characters. This can be useful when working with data that has been imported from external sources, as these non-printable characters can sometimes cause issues when working with the data.
For example, if you have a text string that includes a line break (which is a non-printable character), the CLEAN function will remove the line break and return the remaining text as a single continuous string.
Note:
The CLEAN function does not remove all special characters from a text string, only those that are non-printable. If you need to remove specific special characters, you may need to use other functions such as SUBSTITUTE or CHAR to replace them with a different character or remove them entirely.
What are Non-Printable Characters?
The CLEAN function in Excel removes all non-printable ASCII characters with character codes between 0 and 31, as well as the non-printable ASCII character 127. These characters are also known as control characters, and they are used to control the formatting and display of text in various contexts.
Some examples of non-printable characters that the CLEAN function can remove include:
- Line breaks (character code 10)
- Carriage returns (character code 13)
- Tabs (character code 9)
- Form feeds (character code 12)
- Vertical tabs (character code 11)
- Null characters (character code 0)
- "ESC" (character code 27)
- "DEL" (character code 127)
Examples
How to Clean an Excel Cell of All Non-Printable Characters
In this example, the non-printable characters are represented by the CHAR function, which returns the character with the specified ASCII code. They are all represented by the little error box character as they are non-printable.
When you apply the CLEAN function to these text strings, it will remove the non-printable characters and return a cleaned-up version of the string.
= CLEAN(A2)