Tanjima Hossain
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.
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.
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.
➤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.
➤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.
Read More: How to Use TEXT Function in Excel (10 Examples)
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.
➤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.
➤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 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.
➤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.
➤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. 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.
➤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.
➤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.
Read More: How to Use REPLACE Function in Excel (3 Suitable Examples)
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.
➤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.
➤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.
Similar Readings
- How to Use CODE Function in Excel (5 Examples)
- Use Excel EXACT Function (6 Suitable Examples)
- How to Use FIXED Function in Excel (6 Suitable Examples)
- Use UPPER Function in Excel (4 Examples)
- How to Use SEARCH Function in Excel (3 Examples)
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.
➤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.
➤Press ENTER
➤Drag down the Fill Handle Tool
Result:
Then, you will get the student names in the First Name column.
Read More: How to Use RIGHT Function in Excel (With 6 Easy Examples)
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.
➤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”
➤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
=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”
➤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
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
=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.
➤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
=AVERAGE(D5:D10)D5:D10 is the range of Marks
Finally, you will get 73.83 as the Average Mark.
10. Using VBA Code
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 SubThe 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.
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.
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.
