Author: Oscar Cronquist Article last updated on April 20, 2022

The CLEAN function deletes nonprintable characters in a value, more specifically, it is designed to delete the first 32 nonprinting characters in 7-bit ASCII code.

The Unicode character set has additional nonprinting characters (values 127, 129, 141, 143, 144 and 157), however, the CLEAN function does not delete these characters.

Formula in cell D3:

=CLEAN(B3)

The picture above shows ANSI codes for each character so you easily can spot the characters that the CLEAN function can delete.

For example, ANSI character 29 is deleted by the CLEAN function above, 29 is among the first 32 nonprinting characters in 7-bit ASCII code.

1. CLEAN Function Syntax

CLEAN(text)

Back to top

2. CLEAN Function Arguments

text Required. A value from which you want to remove nonprintable characters

Back to top

3. CLEAN Function example

CLEAN function example

This example demonstrates the CLEAN function, the image above shows a formula in cell D3 that removes nonprintable characters from cell B3.

Cell B3 contains these characters in ANSI code: 53, 52,29, and 66. Character 29 in ANSI is removed and only 53, 52, and 66 are now left in cell D3.

Nonprintable characters are the first 32 in 7-bit ASCII code.

Formula in cell D3:

=CLEAN(B3)

Back to top

4. CLEAN function - array

CLEAN function array

The image above demonstrates a formula that removes nonprintable characters in an array.

Formula in cell B3:

=CLEAN({"54B";"AB"})

Explaining formula

CLEAN({"54B";"AB"})

returns

{"54B";"AB"}

Back to top

5. CLEAN function - string

CLEAN function string

Excel 365 dynamic array formula in cell D3:

=TEXTSPLIT(CLEAN(B3),,";")

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3)

Step 2 - Split values into an array

TEXTSPLIT(CLEAN(B3),,";")

Back to top

6. Check if a cell contains nonprintable characters

Check if a cell contains nonprintable characters

Formula in cell D3:

=CLEAN(B3)<>B3

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3)

Step 2 - Get source value

B3

Step 3 - Check if not equal

CLEAN(B3)<>B3

Back to top

7. Check if a list contains nonprintable characters

Check if a list contains nonprintable characters

Formula in cell D3:

=OR(CLEAN(B3)<>B3)

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3)

Step 2 - Get source value

B3

Step 3 - Check if not equal

CLEAN(B3)<>B3

Step 4 - Apply OR logic

OR(CLEAN(B3)<>B3)

Back to top

8. Highlight cells containing nonprintable characters

Highlight cells containing nonprintable characters

Conditional Formatting Formula:

=CLEAN(B3)<>B3

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3)

Step 2 - Get source value

B3

Step 3 - Check if not equal

CLEAN(B3)<>B3

How to apply Conditional formatting

Highlight cells containing nonprintable characters setup3

  1. Go to tab "Home" on the ribbon.
  2. Press with left mouse button on the "Conditional Formatting" button, a popup menu appears.
  3. Press with left mouse button on "New Rule...", and a dialog box shows up.
  4. Enter the formula described below the image above.
    Highlight cells containing nonprintable characters setup
  5. Press with left mouse button on the "Format..." button, a new dialog box appears.
  6. Pick a "Fill" color.
  7. Press with left mouse button on OK button to dismiss the dialog box.
  8. You are now back to the first dialog box.
  9. Press with left mouse button on the "OK" button to dismiss this dialog box as well.

Highlight cells containing nonprintable characters setup2

Back to top