CLEAN in Excel (Formula, Examples) | How to Use CLEAN Function in Excel?

Excel Functions Tutorials

Clean Excel Function (Table of Contents)

CLEAN in Excel
CLEAN Formula in Excel
CLEAN in Excel- Uses
CLEAN Excel Function – Illustration
How to Use CLEAN Function in Excel?

CLEAN Function in Excel

The CLEAN function in Excel is used to strip non-printable characters from an input string. It is categorized as a String/Text Function.

Each character, whether printable or non-printable, has a unique number known as its Unicode character code or value. The CLEAN in excel removes the first 32 (non-printable) characters in the 7-bit ASCII code (values 0 through 31) from any given text. Unicode also contains other non-printable characters which are not present in the ASCII character set (values 127, 129, 141, 143, 144, and 157). Some of these Unicode characters are not be removed by the CLEAN function in excel by itself.

CLEAN Formula in Excel

Arguments Used in CLEAN Formula in Excel

text – The text to clean.

The text input can be a text itself given in quotes or cell references. It can be a part of a CLEAN formula.

Output

The CLEAN in Excel returns a string/text value after removing the non-printable characters from the string.

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

CLEAN in Excel- Uses

The CLEAN in Excel is used to pre-process file/text which is imported from other applications and contains characters that are not printable/readable with the current application. Imported data from databases, text files or web pages usually contain such characters. These characters may lead to an error when given as input to some functions. Before analyzing such imported data, it needs to be cleaned up

You can use CLEAN to remove characters that are not printable from the text. You can also use CLEAN to strip line breaks from the text.

Sometimes, these non-printable characters are not visible at all. However, they still need to be trimmed from the text for other functions/applications to work on it.

CLEAN Excel Function – Illustration

Suppose you have a text containing a non-printable character in cell C3 =”This is a test string” & CHAR(7) & “.”.

To remove the non-printable character and return the clean text, type “= CLEAN( C3 )”

and press Enter. It will remove the non-printable character.

You can also directly write the text instead of giving as a cell reference.

= CLEAN( “This is a test string” & CHAR(7) & “.”)

However, this format is rarely used with this CLEAN function in Excel.

It will also give the same output.

How to Use CLEAN Function in Excel?

Excel CLEAN function in Excel is very simple and easy to use. Let understand the working of CLEAN in excel by some examples.

CLEAN in Excel Example #1

Suppose you have some text given in a range of cells ( C3:C7 ). Now, you want to clean this text by removing the non-printable characters.

You can use the following syntax:

=CLEAN( C3 )

and press Enter.

Now, you can drag it to the rest of the cells.

CLEAN in Excel Example #2

Suppose you have some text given in a range of cells (C3:C7). The data includes non-printable characters, line breaks, and some trailing and leading extra spaces. You are required to clean this text by removing the non-printable characters, line breaks, and trailing and leading extra spaces.

To Excel clean and remove the line breaks, extra spaces and non-printable characters from the given data, you can use the following syntax:

=TRIM( CLEAN( C3 ))

and press Enter.

=CLEAN(C3) will remove non-printable characters.

TRIM(..) will remove unnecessary spaces after the non-printable characters have been removed by the Excel CLEAN function.

CLEAN in Excel Example #3

Suppose you have a text given in a range of cells ( C3:C6 ). Now, you want to check if any of these cells contain any non-printable characters. If yes, then get a count of these characters.

To do this, you can use the following syntax:

=IF( ( LEN( C3 ) – LEN( CLEAN( C3 )) ) > 0, ( LEN( C3 ) – LEN(CLEAN( C3 )) ) & ” Non-printable characters”, “Clean text”)

and press Enter.

Let us look at the Excel CLEAN function in detail:

CLEAN( C3 ) will strip the non-printable characters.
LEN( CLEAN( C3 ) ) will give the length of the string after removing non-printable characters from C3.
LEN( C3 ) will give the length of the string C3.
LEN( C3 ) – LEN( CLEAN(C3) ) will give the number of non-printable characters in the string C3.

Finally,

If the number of non-printable characters is greater than 0

then, it will print è { Number of non-printable characters } “Non-printable characters”

else

it will print “Clean text”.

You can now drag it to rest of the cells.

CLEAN in Excel Example #4

Suppose you have some text in the cells B3:B7. The text contains some non-printable characters, line breaks, leading and trailing spaces, and a special character CHAR(160), which is a non-breaking space typically used in websites. You need to remove these characters from the text.

Since, the Excel CLEAN function in insufficient to strip CHAR(160), we need to use the substitute function in this case, You may use the following syntax:

=TRIM( CLEAN( SUBSTITUTE(B3, CHAR(160), ” “) ) )

In this,

SUBSTITUTE( B3, CHAR(160), ” “) will substitute CHAR(160) from the text.

CLEAN ( SUBSTITUTE( B3, CHAR(160), ” “) ) will clean the substituted text.

TRIM( CLEAN( SUBSTITUTE( B3, CHAR(160), ” “) )) will remove unnecessary spaces after the non-printable characters and CHAR(160) have been removed.

Similarly, You can now drag it to the rest of the cells.

CLEAN in Excel Example #5

Suppose you have some numeric data which contains some non-printable characters in between. You need to remove these non-printable characters from your text in order to use the mathematical operations.

To remove these characters, you can simply use the syntax:

=CLEAN( C3 )

and press Enter.

You may notice that the Excel CLEAN function returns a text. So, the output may not behave as a numeric text and give an error with some mathematical operations in some cases. If that is the case, you can alternatively use the following syntax:

=VALUE( CLEAN( C3 ))

and press Enter.

VALUE () will convert the input text into the numeric format.

Similarly, You can now drag it to the rest of the cells.

Things to Remember About CLEAN Function in Excel

The Excel CLEAN function removes the non-printable characters from a string.
It removes 0 to 31 7-bit ASCII code from the input text.
Some Unicode characters not present in the ASCII code may not be removed by the Excel CLEAN function.
Some non-printable characters may not be visible but, still exist in the text. The Excel CLEAN function may be used to check their existence.

You can download this CLEAN Function in Excel template here – CLEAN Function Excel Template

CLEAN Excel Function