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.

What to Know

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

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.

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.

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.

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?

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.

Get the Latest Tech News Delivered Every Day