For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Text

CLEAN Function

Removes most nonprintable characters from text, along with some printable characters


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

Syntax

CLEAN(text)

  • text  Required.  Any text from which you want to remove nonprintable characters in a cell .

Applies To

Excel 2003 and above

Remarks

CLEAN removes characters generated by 36 ASCII codes. This figure shows the codes and the characters they display with the Calibri font.

Examples of the characters that Excel's CLEAN function removes

Note that CLEAN does not remove one non-printable character, ASCII 160, the non-breaking space that's used extensively on the Web. Use SUBSTITUTE to remove that character, as shown in the Examples below.

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

Examples for Excel's CLEAN function

The CHAR function generates a printable and an unprintable character in column B. Because the LEN of both CLEANed versions is zero in column D, we know that CLEAN removes the characters.

Examples for Excel's CLEAN function

Because the LEN of the CLEANed version of CHAR(160) equals 1, we know that CLEAN fails to remove it. We therefore use SUBSTITUTE to remove the character.

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards