The Excel CLEAN function is a Text formula that removes "non-printable" characters from a text string. The function is especially useful for eliminating unsupported characters from a text that was imported from an external source. In this guide, we’re going to show you how to use the Excel CLEAN function and also go over some tips and error handling methods.
Supported versions
- All Excel versions
Excel CLEAN Function Syntax
CLEAN(text)
Arguments
text |
The text that you want to clean. |
Example
You can enter a static string or a cell reference for the CLEAN function parameter. For example,
=CLEAN("2 + 2
equals
5")
Tips
- Use the CLEAN function to easily remove non-printable characters on text strings that are imported from external sources, like an HTML page.
- The Excel CLEAN function will remove non-printable 7-bit ASCII characters (values 0 through 31) from the specified text. The non-printable characters in Unicode character set (values 127, 129, 141, 143, 144, and 157) will be ignored by the formula.
- To remove leading, trailing, or extra spaces (ASCII code 32), use the TRIM function instead.