What is the CLEAN function?
The CLEAN function deletes nonprintable characters from a value in 7-bit ASCII code.

Why delete nonprintable characters?
Sometimes data imported from other software may contain characters that don't print with your operating system.

What is a nonprintable character?
The first 32 nonprinting characters in the 7-bit ASCII code from 0 (zero) to 31. There are more nonprinting characters in the Unicode character set more specifically values 127, 129, 141, 143, 144, and 157.

What is 7-bit ASCII code?
ASCII stands for American Standard Code for Information Interchange. There are 256 characters in the ASCII code set, numbered from 0 to 255. The standard characters like as letters, digits, and symbols are the same on all computers and occupy the first 128 positions 0 to 127. The remaining characters depend on the selected language and regional settings 128 to 255.

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

Formula in cell D3:

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

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:

Back to top

4. CLEAN function - array

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

Formula in cell B3:

Explaining formula

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

returns

{"54B";"AB"}

Back to top

5. CLEAN function - string

This formula cleans a cell value containing strings separated by a semicolon, the result is an array that is spilled to cells below.

Excel 365 dynamic array formula in cell D3:

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3)

becomes

CLEAN("54B;AB")

and returns

"54B;AB"

Step 2 - Split values into an array

The TEXTSPLIT function splits a string into an array based on delimiting values.

Function syntax: TEXTSPLIT(Input_Text, col_delimiter, [row_delimiter], [Ignore_Empty])

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

becomes

TEXTSPLIT("54B;AB")

and returns {"54B;AB"}.

Back to top

6. Check if a cell contains nonprintable characters

The following formula checks if a cell value contains nonprintable characters.

Formula in cell D3:

It returns TRUE if the cell value contains nonprintable characters.

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3)

becomes

CLEAN("54B")

and returns

"54B".

Step 2 - Get source value

B3 returns "54B".

Step 3 - Check if not equal

The less than and larger than signs let you compare value to value, it returns TRUE if they are not a match.

CLEAN(B3)<>B3

becomes

"54B"<> "54B"

and returns TRUE.

Back to top

7. Check if a cell range contains nonprintable characters

The following formula checks if values in a cell range contains nonprintable characters. It returns TRUE if it contains nonprintable characters.

Formula in cell D3:

This example checks if cell range B3:B5 contains nonprintable characters. Cells C3:C5 shows the characters' corresponding ANSI code, the CLEAN function removes characters 0 (zero) to 31 in the ANSI character set coding.

Explaining formula

Step 1 - Remove nonprintable characters

CLEAN(B3:B5)

becomes

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

and returns

{" 5 B";"54B";"54  B"}

Step 2 - Get values from cell range

B3:B5

returns

{" 5 B";"54B";"54  B"}

Step 3 - Check if not equal

The less than and larger than signs let you compare value to value, it returns TRUE if they are not a match.

CLEAN(B3:B5)<>B3:B5

becomes

{" 5 B";"54B";"54  B"}<>{" 5 B";"54B";"54  B"}

and returns

{FALSE; TRUE; FALSE}.

Step 4 - Apply OR logic

The OR function evaluates a logical expression in each argument and if at least one argument returns TRUE the OR function returns TRUE. If all arguments return FALSE the OR function also returns FALSE.

Function syntax: OR(logical1, [logical2])

OR(CLEAN(B3:B5)<>B3:B5)

becomes

OR({FALSE; TRUE; FALSE})

and returns TRUE.

Back to top

8. Highlight cells containing nonprintable characters

The image above shows a conditional formatting formula highlighting cells that contain nonprintable characters.

Conditional Formatting Formula:

This formula is the same formula as in section 6. Check it out if you want a detailed explanation.

How to apply Conditional formatting

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.
   
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.

Back to top

Useful resources

Clean function - Microsoft support
Clean Function to Clean Entire Sheet
How to combine two Excel functions (TRIM and CLEAN) in one formula?

Functions in 'Text' category

The CLEAN function function is one of many functions in the 'Text' category.

Excel function categories

Excel categories

Leave a Comment