How to use the CLEAN function
What is the CLEAN function?
The CLEAN function deletes non-printable characters from a value in 7-bit ASCII code.
Table of Contents
1. Introduction
Why delete non-printable characters?
Sometimes data imported from other software may contain characters that don't print with your operating system.
What is a non printable character?
The first 32 non-printing characters in the 7-bit ASCII code from 0 (zero) to 31. There are more non-printing 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 non-printing characters from the Unicode character set?
The CLEAN function does not delete the Unicode character set which has additional non-printing 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 non-printing characters in 7-bit ASCII code.
2. Syntax
CLEAN(text)
| text | Required. A value from which you want to remove non-printable characters |
3. Example
This example demonstrates the CLEAN function, the image above shows a formula in cell D3 that removes non-printable 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.
Formula in cell D3:
Non-printable characters are the first 32 in 7-bit ASCII code.
4. CLEAN function - array
The image above demonstrates a formula that removes non-printable characters in an array. The argument contains two values in the array.
Formula in cell B3:
Both values contain non-printable characters which are removed in the output values. The corresponding cells in column C show the ANSI code for each character in the output value.
Explaining formula
CLEAN({"54 B";"A B"})
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 which doesn't contain non-printable characters.
Excel 365 dynamic array formula in cell D3:
Explaining formula
Step 1 - Remove non-printable characters
CLEAN(B3)
becomes
CLEAN("54 B;A B")
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 non-printable characters
The following formula checks if a cell value contains non-printable characters. It returns TRUE if the cell value contains non-printable characters.
Formula in cell D3:
The value in cell B3 contains a non-printable character, the corresponding ANSI characters are displayed in cell C3. The formula in cell D4 returns TRUE meaning there is a t least one non-printable character in cell B3.
Explaining formula
Step 1 - Remove non-printable characters
CLEAN(B3) becomes CLEAN("54 B")
and returns "54B".
Step 2 - Get source value
B3 returns "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)<>B3
becomes
"54B"<> "54 B"
and returns TRUE.
7. Check if a cell range contains non-printable characters
The following formula checks if values in a specific cell range contains non-printable characters. It returns TRUE if it contains at least one non-printable character.
Formula in cell D3:
This example checks if cell range B3:B5 contains non-printable 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 non-printable characters
CLEAN(B3:B5)
becomes
CLEAN({" 5 B";"54 B";"54 B"})
and returns
{" 5 B";"54B";"54 B"}
Step 2 - Get values from cell range
B3:B5
returns
{" 5 B";"54 B";"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";"54 B";"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 non-printable characters
The image above shows a conditional formatting formula highlighting cells that contain non-printable characters.
Conditional Formatting Formula:
This formula is the same formula as in section 6.
Explaining formula
Step 1 - Remove non-printable characters
CLEAN(B3) becomes CLEAN("54 B")
and returns "54B".
Step 2 - Get source value
B3 returns "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)<>B3
becomes
"54B"<> "54 B"
and returns FALSE. Cell B3 does not contain at least one non-printable character.
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 29 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