Excel Clean Function

Businessman using a photocopy machine in an office
Eric Audras / Getty Images

Use the CLEAN function to remove a number of non-printable computer characters that have been copied or imported into a worksheet along with good data. Some common examples of these non-printable characters are within the images of this tutorial.

Such characters can interfere with using the data in worksheet operations such as printing, sorting, and filtering data. This low-level function is frequently found at the beginning and/or end of data files for maintenance purposes.

This tutorial has been tested and confirmed for Microsoft Excel 2007, 2010, and 2016.

CLEAN Away 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 and they are referred to as control characters used by programs to control peripheral devices such as printers. As such, they are not intended for use in a worksheet and can cause a number of errors when present.

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

CLEAN Function Syntax and Arguments

Screenshot of Excel with the CLEAN function highlighted

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

= CLEAN ( Text )

Text: (required) the data to be cleaned of non-printable characters. A cell reference to the location of this data in the worksheet.

For example, to clean the data in cell A2 in the image above, enter the formula into another worksheet cell:

=CLEAN(A2)

If used to clean number data, the CLEAN function, in addition to removing any non-printing characters, will convert all numbers to text, which may result in errors if that data is then used in calculations.

Non-Printable Characters

In column A in the image, the CHAR function has been used to add non-printing characters to the word text as shown in the formula bar above the worksheet for cell A3 that are then removed with the CLEAN function.

In columns B and C of the image, the LEN function, which counts the number of characters in a cell, is used to show the effect of using the CLEAN function on the data in column A.

The LEN function is simply to showcase the character count after the CLEAN function has been run; it is not required when cleaning your Excel data.

  • Character count for cell B2 is 6 because there are four characters for the word text and two for the non-printing characters surrounding it.
  • Character count for cell C2 is 4 because the CLEAN function has been added to the formula and strips away the two non-printing characters before the LEN function counts the characters.

Removing Non-Printable, Non-ASCII Characters

Screenshot of Excel with the SUBSTITUTE option being utilized

While the CLEAN function is excellent for removing non-printable ASCII characters, there are a few characters that fall outside of the ASCII range that you may wish to remove due to them also being non-printable.

Non-printable Unicode characters include numbers 129, 141, 143, 144, and 157. Additionally, you may wish to remove number 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 then remove, as seen above in row 3 where character 132 is replaced by character 7, and then cleaned away.

 =LEN(CLEAN(SUBSTITUTE(A3,CHAR(132),CHAR(7)))) 

Alternately, one can simply substitute the offending non-printable character with nothing as shown in row 3.

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