CLEAN Function

The CLEAN function accepts a text string and returns text that has been "cleaned" of line breaks and other non-printable characters. You can use CLEAN to strip non-printing characters and strip line breaks from text. For example, to clean text in cell A1:

=CLEAN(A1) // clean text in A1

The CLEAN function accepts just one argument, text, which can be a text string or number. CLEAN removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 through 31), if any are found, and returns the result. Text without these characters is returned unchanged. Note that CLEAN will remove line breaks if found.

CLEAN will not remove extra space characters. To remove extra space, use the TRIM function. You can use CLEAN and TRIM together in one formula like this:

=TRIM(CLEAN(A1)) // clean and remove extra space

ASCII limitation

The CLEAN function removes the first 32 (non-printable) characters in the 7-bit ASCII code (values 0 through 31) from text. Unicode contains other non-printable characters that are not removed. To remove specific characters beyond the first 32 ASCII characters, you can use the SUBSTITUTE function with the CHAR function. For example to remove character 202:

=SUBSTITUTE(A1,CHAR(202),"") // remove character 202

You can use the CODE function to determine the number for a problematic character, then use that number inside CHAR to return the character in a formula. See this page for more information.