How to Use Excel's Clean Function

Remove non-printable ASCII and Unicode characters

By
Ted French
Ted French
Writer
Former Lifewire writer Ted French is a Microsoft Certified Professional who teaches and writes about spreadsheets and spreadsheet programs.
lifewire's editorial guidelines
Updated on February 4, 2022

What to Know

  • To remove ASCII characters, enter =CLEAN(Text).
  • Use SUBSTITUTE function to convert Unicode characters into ASCII characters.

Here's how to use the Clean function in Microsoft Excel 2019, 2016, 2013, 2010, and Excel for Microsoft 365. The Clean function removes many non-printable computer characters copied or imported into a worksheet because such characters can interfere with printing, sorting, and filtering data.

CLEAN Function Syntax and Arguments

A function's syntax is its layout and includes the name, brackets, and arguments. The syntax for the CLEAN function is: 

=CLEAN(Text)

Text

(required) is a 

cell reference

 to the location of this data in the worksheet you want to clean up.

For example, say cell A2 contains this formula: 

=CHAR(10)&"Calendar"&CHAR(9)

To clean that, you would enter the formula into another worksheet cell: 

=CLEAN(A2)

The result would leave just the word

Calendar

in cell A2.

In addition to removing non-printing characters, the CLEAN function also converts numbers to text, which may result in errors if you later use that data in calculations.

Removing Non-Printable, Non-ASCII Characters

While the CLEAN function is excellent for eliminating non-printable ASCII characters, there are a few non-printable characters that fall outside of the ASCII range that you might wish to remove.

Non-printable Unicode characters include numbers 129, 141, 143, 144, and 157. Additionally, you may wish to remove 127, which is the delete character and is also non-printable.

One way to remove such data is to have the SUBSTITUTE function convert it into an ASCII character that the CLEAN function can remove. You can nest the SUBSTITUTE and CLEAN functions to make it easier. 

=CLEAN(SUBSTITUTE(A3,CHAR(129),CHAR(7)))

Alternately, one can simply substitute the offending non-printable character with nothing (""). 

=SUBSTITUTE(A4,CHAR(127),"")

What Are Non-Printable Characters?

ASCII Character Table

Each character on a computer — printable and non-printable — has a number known as its Unicode character code or value. Another, older, and better-known character set is ASCII, which stands for the American Standard Code for Information Interchange, has been incorporated into the Unicode set.

As a result, the first 32 characters (0 to 31) of the Unicode and ASCII sets are identical. They are used by programs to control peripheral devices such as printers, across different platforms. As such, they are not intended for use in a worksheet and can cause errors when present.

The CLEAN function, which predates the Unicode character set, removes the first 32 non-printing ASCII characters, and the same characters from the Unicode set.

Was this page helpful?