For example, you can employ the Excel CLEAN function to remove frequent low-level computer code that appears at the beginning and the end of the data files and cannot be printed.

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.

Syntax

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 textConvert All Numbers To TextYou may need to convert numbers to text in Excel for a variety of reasons. If you use Excel spreadsheets to store long and not-so-long numbers, or if you don’t want the numbers in the cells to be involved in calculating, or if you want to display leading zeros in numbers in cells, you’ll need to convert them to text at some point. It’s useful for displaying numbers in a more readable format or combining numbers with text or symbols. The methods listed below can be used to accomplish this.read more, resulting in errors if that data is further used in calculations.

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) & “.”.

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

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

We can also directly write the text instead of giving it as a cell referenceCell ReferenceCell reference in excel is referring the other cells to a cell to use its values or properties. For instance, if we have data in cell A2 and want to use that in cell A1, use =A2 in cell A1, and this will copy the A2 value in A1.read more.

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

We can use the following syntax:

=CLEAN( C3 )

Then, press the “Enter” key.

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

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.

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(C3) will remove non-printable characters.

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

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.

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”)

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.

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.

Since the Excel CLEAN function is insufficient to strip CHAR(160), we need to use the SUBSTITUTE function in ExcelSubstitute Function In ExcelSubstitute function in excel is a very useful function which is used to replace or substitute a given text with another text in a given cell, this function is widely used when we send massive emails or messages in a bulk, instead of creating separate text for every user we use substitute function to replace the information.read more. In this case, we 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, we can now drag it to the rest of the cells.

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.

To remove these characters, we can use the syntax:

Then, we must press the “Enter” key.

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

Now, we must press the “Enter” key.

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

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.

CLEAN Excel Function Video

This article is a guide to CLEAN Function in Excel. We discuss the Excel CLEAN function and how to use it, along with examples and downloadable templates. You may also look at these useful functions in Excel: –

  • Write Text File using VBALine Breaks in ExcelTIME in ExcelREPLACE Excel FunctionWeek Number in Excel