Syntax. CLEAN(text)
Definition. This function deletes all nonprintable characters from text.
Arguments
text (required). The text to be cleaned
Background. Use the CLEAN() function for text imported from other applications that might include nonprintable characters. You can also use CLEAN() to delete code at the beginning and the end of a data file, or to remove unwanted characters such as line breaks.
The function removes mainly characters within the code range 1 through 31 (see the CODE() and CHARS() functions).
Example. Sometimes you might need to delete line breaks from data lists to export a list in a text format. Assume that cell A2 contains the following two text lines:
"August ¶ the Strong"
=CLEAN(A2) results in one line with the text “August the Strong”, which you can export without any problems.
Another example is the formula
=CLEAN("inter"&CHAR(13)&"continental"&CHAR(32)&"flight")which removes the line break CHAR(13) and keeps the space CHAR(32): intercontinental flight.