How to Use Excel's Clean Function

A man using a photocopy machine

 Eric Audras/Getty Images

You can use the CLEAN function to remove many non-printable computer characters that have been copied or imported into a worksheet along with printable data.

Such characters can interfere with using the data in worksheet operations such as printing, sorting, and filtering data.

These instructions apply to Microsoft Excel 2019, 2016, 2013, 2010, and Excel for Office 365.

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.

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),"")