Syntax. CLEAN(text)

Definition. This function deletes all nonprintable characters from text.


  • 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 “ ...

Get Microsoft® Excel® 2010 Formulas & Functions Inside Out now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.