CLEAN Excel Function

Last Updated :

-

Blog Author :

WallStreetMojo Team

Edited by :

Ashish Kumar Srivastav

Reviewed by :

Dheeraj Vaidya, CFA, FRM

Table Of Contents

arrow

CLEAN Function in Excel

The CLEAN function in Excel strips non-printable characters from an input string. It is categorized as a string/text function.

For example, suppose we want

Whether printable or non-printable, each character 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. However, Unicode also contains other non-printable characters not present in the ASCII character set (values 127, 129, 141, 143, 144, and 157). Some of these Unicode characters are not removed by the CLEAN function in Excel by itself.

Table of contents

Syntax

CLEAN Formula

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 we use the CLEAN function in Excel to clean number data, removing any non-printing characters will convert all numbers to text, resulting in errors if that data is further used in calculations.

CLEAN Excel Function in Video

Uses of CLEAN Function

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

We can use Excel CLEAN function to remove characters that are not printable from the text. In addition, we can also use the CLEAN function to strip line breaks from the text.

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

Illustration

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

CLEAN Function Example1

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

CLEAN Function illustration 1

Press the "Enter." As a result, it will remove the non-printable character.

CLEAN Function illustration 2

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

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

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

CLEAN Function illustration 3

It will also give the same output.

CLEAN Function illustration 4

How to Use the CLEAN Function in Excel?

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

Example #1

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

CLEAN Example 1

We can use the following syntax:

=CLEAN( C3 )

CLEAN Example 1-1

Then, press the “Enter” key.

CLEAN Example 1-2

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

CLEAN Example 1-3

CLEAN Example 1-4

Example #2

Suppose we 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. We must clean this text by removing the non-printable characters, line breaks, and trailing and leading additional spaces.

CLEAN Example 2

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

=TRIM( CLEAN( C3 ))

CLEAN Example 2-1

Then, press the “Enter” key.

CLEAN Example 2-2

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

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

CLEAN Example 2-3
CLEAN Example 2-4

Example #3

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

CLEAN Example 3

To do this, we can use the following syntax:

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

CLEAN Example 3-1

Then, press the “Enter” key.

CLEAN Example 3-2

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

We can now drag it to the rest of the cells.

CLEAN Example 3-3
CLEAN Example 3-4

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), a non-breaking space typically used in websites. You need to remove these characters from the text.

CLEAN Example 4

Since the Excel CLEAN function is insufficient to strip CHAR(160), we need to use the SUBSTITUTE function in Excel. In this case, we may use the following syntax:

=TRIM( CLEAN( SUBSTITUTE(B3, CHAR(160), " ") ) )

CLEAN Example 4-1

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.

CLEAN Example 4-2

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

CLEAN Example 4-3
CLEAN Example 4-4

Example #5

Suppose we have some numeric data which contains some non-printable characters in between. We need to remove these non-printable characters from our text to use the mathematical operations.

CLEAN Example 5

To remove these characters, we can use the syntax:

=CLEAN( C3 )

CLEAN Example 5-1

Then, we must press the “Enter” key.

CLEAN Example 5-2

We may notice that the Excel CLEAN function returns a text. So, the output may not behave as a numeric text and sometimes give some mathematical operations. If that is the case, we can alternatively use the following syntax:

=VALUE( CLEAN( C3 ))

CLEAN Example 5-3

Now, we must press the “Enter” key.

CLEAN Example 5-4

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

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

CLEAN Example 5-5
CLEAN Example 5-6

Things to Remember

  • 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. We may use the Excel CLEAN function to check their existence.