How to Use CLEAN Function in Excel (10 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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


CLEAN Function in Excel: 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.


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.

Excel CLEAN Function


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.

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

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

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

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

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

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

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

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 CLEAN Function in Excel VBA to Remove Non-printable Characters

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.


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.


<< Go Back to Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo