How to Use CLEAN Function in Excel (10 Examples)

The CLEAN function is an Excel text function that is used to remove non-printable characters or line breaks from texts of a dataset. In this article, you will be able to know the details of the CLEAN function and get to know some uses of this function with some examples.

CLEAN Function in Excel: Syntax

⦿ Function Objective

The CLEAN function removes all of the non-printable characters from text.

⦿ Syntax

=CLEAN(text)

⦿ Arguments

⦿ Return Value

After Entering the function, it will give you the text string free from all of the non-printable characters.

⦿ Version

The CLEAN Function is introduced in Excel 2000 version and is available for all versions after that.

Note

The CLEAN function can only remove the non-printable characters represented by numbers 0 to 31 in the 7-bit ASCII code.

Here, we will use the following data table of a college in which we have some non-printable characters in the imported data from another source. By using this table we will explain different examples of this function.

For creating the article, we have used Microsoft Excel365 version, you can use any other versions according to your convenience.

1. Using CLEAN Function in Excel to Remove Non-printable Characters

Here, we have some Email Ids which have some non-printable characters like CHAR(15), CHAR(12) and by using the CLEAN function we will remove them and get the Real Email Ids.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Email Id from which we want to remove the non-printable characters.

➤Press ENTER

➤Drag down the Fill Handle Tool

Result:
In this way, you will get the Real Email Id which is free from all of the non-printable characters.

2. Removing Line Break with the CLEAN Function in Excel

Suppose, you have some line break between the first name and the last name of the students and you want to remove these line breaks by using the CLEAN function.

➤Select the output cell D5
➤Type the following formula

C5 is the Student Name from which you want to remove the line break.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
After that, you will get the Student Names which are free from all of the line breaks.

3. Using the CLEAN and TRIM Functions to Remove Extra Space in Excel

Sometimes, you may have extra space in the text string which is CHAR(32) and as the CLEAN function can not remove this so, here we are using the TRIM function along with the CLEAN function to remove CHAR(15), CHAR(12) and CHAR(32) from the Imported Email Id below.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Email Id from which we want to remove the non-printable characters.
TRIM will remove all of the extra spaces from the text string.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
In this way, you will get the Real Email Id which is free from all of the non-printable characters.

4. Combining the CLEAN and SUBSTITUTE Functions to Remove Non-printable Characters

Sometimes, you may have non-printable characters which can not be removed by the CLEAN function. In the Imported Email Id column, we have some non-breaking spaces which are CHAR(160). To remove these types of non-printable characters we can use the SUBSTITUTE function along with the CLEAN function and the TRIM function.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Email Id from which we want to remove the non-printable characters.

SUBSTITUTE will replace CHAR(160) with a Blank, CLEAN will remove the non-printable characters CHAR(15), CHAR(12) and TRIM will remove all of the extra spaces from the text string.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
After that, you will get the Real Email Id which is free from all of the non-printable characters.

5. Using the Excel CLEAN and LEN Functions to Count the Number of Characters

Here, we will count the number of characters of the Student Names by using the LEN function after removing the line breaks with the help of the CLEAN function.

➤Select the output cell D5
➤Type the following formula

C5 is the Student Name whose character length you want to count.

CLEAN will remove the line break and TRIM will remove all of the extra spaces from the text string. After that LEN will count the character length.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
Afterward, you will be able to count the number of characters of the Student’s Names.

6. Using the CLEAN and LEFT Functions to Extract Text in Excel

Let’s say, you want to extract the name portion from the Imported Email Id and to do this you can use the CLEAN function and the LEFT function.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Email Id.

CLEAN will remove the non-printable characters and FIND(“@”, CLEAN(C5),1) will give the position of “@” in the text string. So, FIND will return 6 and then 6-1=5 will be the number of characters in the LEFT function.

After that LEFT will extract the first five characters from the clean Email Id.

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
Then, you will get the student names in the First Name column.

7. Replacing Characters with SUBSTITUTE and CLEAN Functions in Excel

Here, we will create a New Email Id by removing the non-printable characters and replacing “gmail” with “yahoo” from the Imported Email Id column.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Email Id.
SUBSTITUTE will replace “gmail” with a “yahoo”

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:
In this way, you will get the New Email Ids.

8. Checking If the Texts Need to be Cleaned

For a large dataset, it may be tiresome to find out which texts you need to clean. But it can be done easily if you can check out easily which text strings are to be cleaned. To do this here we are using the IF function.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Email Id.

CLEAN(C5)=C5 is the logical test which means the text string is equal to the text string removed by all of the non-printable characters. When it is TRUE then IF will return “Cleaned” otherwise “Not Cleaned”

➤Press ENTER
➤Drag down the Fill Handle Tool

Result:

Then, you will get to know which text strings you have to clean.

9. Getting Average of Values Using the CLEAN and VALUE Functions

Here, we have some marks but which have some non-printable characters and you can remove them by using the CLEAN function. But after that, the numbers will turn into the texts so you cannot get the Average Marks without converting them into values by using the VALUE function.

➤Select the output cell D5
➤Type the following formula

C5 is the Imported Marks.

CLEAN will remove the unnecessary part of the data (but convert the data to text) and then, VALUE will convert the text strings into numbers.

➤Press ENTER
➤Drag down the Fill Handle Tool

After that, you will get the marks which are in number format.

To get the Average Marks, type the following formula in the cell D11

D5:D10 is the range of Marks

Finally, you will get 73.83 as the Average Mark.

10. Using CLEAN Function in Excel VBA to Remove Non-printable Characters

The CLEAN function can also be used in the VBA code.

➤Go to Developer Tab>>Visual Basic Option

Then, the Visual Basic Editor will open up.
➤Go to Insert Tab>> Module Option

After that, a Module will be created.

➤Write the following code

Sub cleanspace()

  Range("D5") = Application.WorksheetFunction.Clean(Range("C5"))
  Range("D6") = Application.WorksheetFunction.Clean(Range("C6"))
  Range("D7") = Application.WorksheetFunction.Clean(Range("C7"))
  Range("D8") = Application.WorksheetFunction.Clean(Range("C8"))
  Range("D9") = Application.WorksheetFunction.Clean(Range("C9"))
  Range("D10") = Application.WorksheetFunction.Clean(Range("C10"))

End Sub

The cells C5 to C10 of Column C will be free from the non-printable characters because of the CLEAN function and we will get the outputs in corresponding cells of Column D.

➤Press F5

Result:
In this way, you will get the Real Email Id which is free from all of the non-printable characters.

Things to Remember

🔺 This function removes only 0 to 31 7-bit ASCII code from the given text.

🔺 The CLEAN function can also remove some non-printable characters which are not visible.

🔺 It can not remove the non-printable characters which are not present in the ASCII code.

Download Workbook

Conclusion

In this article, we tried to cover the introduction and usage of the CLEAN function in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.