Exceljet

Quick, clean, and to the point

Excel CLEAN Function

Excel CLEAN function
Summary 

The Excel CLEAN function takes a text string and returns text that has been "cleaned" of line breaks and other non-printable characters.

Purpose 
Strip non-printable characters from text
Return value 
Text with non-printable characters removed.
Syntax 
=CLEAN (text)
Arguments 
  • text - The text to clean.
Version 
Usage notes 

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. See this page for more information.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.