The Excel CLEAN Function

Related Function:
TRIM Function

Basic Description

The Excel CLEAN function removes all non-printable characters from a supplied text string.

The function is designed to remove the non-printable characters, represented by numbers 0 to 31 of the 7-bit ASCII code, which are often found at the start of data that has been imported into Excel from other applications.

The syntax of the Clean function is:

CLEAN( text )

where the text argument is the text string that you want to remove the non-printable characters from.

Clean vs. Trim Function

Note that the Clean function is similar to the Excel Trim Function. The two functions differ in that the Clean Function removes the non-printable characters, represented by the ASCII numerics codes 0 to 31, while the Trim function removes additional spaces (represented by the ASCII numeric code 32).

Clean Function Examples

The example below shows the Excel Clean function used on four different text strings.

Excel Clean Function Examples

All four of the above examples give the same result, which is the string,

"clean text"

Further information and examples of the Excel Clean function can be found on the Microsoft Office website.