How to use the CLEAN function
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).
Table of Contents
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)
2. CLEAN Function Arguments
text | Required. A value from which you want to remove nonprintable characters |
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:
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"}
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"}.
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.
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.
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
- Go to tab "Home" on the ribbon.
- Press with left mouse button on the "Conditional Formatting" button, a popup menu appears.
- Press with left mouse button on "New Rule...", and a dialog box shows up.
- Enter the formula described below the image above.
- Press with left mouse button on the "Format..." button, a new dialog box appears.
- Pick a "Fill" color.
- Press with left mouse button on OK button to dismiss the dialog box.
- You are now back to the first dialog box.
- Press with left mouse button on the "OK" button to dismiss this dialog box as well.
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.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form