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.

Excel CLEAN Function

Download Workbook


CLEAN Function: Summary & Syntax

⦿ Function Objective

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

⦿ Syntax

=CLEAN(text)

⦿ Arguments

Argument Required/Optional Explanation
text Required It is the text string from which you want to remove all of the non-printable characters

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


10 Examples of using the CLEAN Function in Excel

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 Excel 365 version, you can use any other versions according to your convenience.

Excel CLEAN Function


1. Removing 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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=CLEAN(C5)

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

removing non-printable characters

➤Press ENTER

removing non-printable characters

➤Drag down the Fill Handle Tool

removing non-printable characters

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

Excel CLEAN Function


2. Removing Line Break

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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=CLEAN(C5)

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

removing line break

➤Press ENTER
➤Drag down the Fill Handle Tool

removing line break

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

removing line break


3. Using the CLEAN Function and TRIM Function

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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=TRIM(CLEAN(C5))

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.

removing extra space

➤Press ENTER
➤Drag down the Fill Handle Tool

removing extra space

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

removing extra space


4. Using the CLEAN Function and SUBSTITUTE Function

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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

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

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.

SUBSTITUTE function

➤Press ENTER
➤Drag down the Fill Handle Tool

SUBSTITUTE function

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

Excel CLEAN Function


5. Using the CLEAN Function and LEN Function

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.

number of characters

➤Select the output cell D5
➤Type the following formula

=LEN(TRIM(CLEAN(C5)))

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.

number of characters

➤Press ENTER
➤Drag down the Fill Handle Tool

number of characters

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

Excel CLEAN Function


6. Using the CLEAN Function and LEFT Function

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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=LEFT(CLEAN(C5),FIND("@",CLEAN(C5),1)-1)

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.

extracting characters

➤Press ENTER
➤Drag down the Fill Handle Tool

extracting characters

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

Excel CLEAN Function


7. Replacing Characters

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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=SUBSTITUTE(CLEAN(C5),"gmail","yahoo")

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

replacing characters

➤Press ENTER
➤Drag down the Fill Handle Tool

replacing characters

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

replacing characters


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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=IF(CLEAN(C5)=C5,"Cleaned","Not Cleaned")

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”

checking if cleaning needs

➤Press ENTER
➤Drag down the Fill Handle Tool

checking if cleaning needs

Result:

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

checking if cleaning needs


9. Getting Average of Values

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.

Excel CLEAN Function

➤Select the output cell D5
➤Type the following formula

=VALUE(CLEAN(C5))

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.

averaging values

➤Press ENTER
➤Drag down the Fill Handle Tool

averaging values

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

averaging values

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

=AVERAGE(D5:D10)

D5:D10 is the range of Marks

averaging values

Finally, you will get 73.83 as the Average Mark.

averaging values


10. Using VBA Code

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

Excel CLEAN Function

➤Go to Developer Tab>>Visual Basic Option

VBA code

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

VBA code

After that, a Module will be created.

VBA code

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

VBA code

➤Press F5

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

Excel CLEAN Function


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.


Practice Section

For doing practice by yourself I have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

practice

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.

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo