CLEAN Function - Excel
Overview
The CLEAN function in Excel is a useful tool for removing non-printable characters from a text. It cleans a text string by removing any characters that cannot be printed such as line breaks, non-printable ASCII characters, or other characters that may cause issues when working with data.
Example:
=CLEAN(CONCATENATE(CHAR(10), "Example"))
Syntax:
=CLEAN(text)
text: This is the text string containing the characters you want to clean.
Example
In the example above, we added a non-printable ASCII character to cell C2 in front of the word “Example”. When we use the CLEAN function to fix this, there are no visible differences in the output data vs the input data, it’s not until we look at the text length where we see that the CLEAN function made an impact on the actual data. The input in cell C2 has a character length of 8 while the output in cell D2 has a character length of 7.