The CLEAN function in Microsoft Excel is a very handy function in case you want to remove non-printable texts/characters or remove line breaks. It has various other uses too. This function is categorized under Excel’s Text functions. In this tutorial we will show you some of the best uses of CLEAN function which will be very useful to you if you import data regularly from internet.
This Content Covers:
- What is the CLEAN Function in Excel?
- Syntax, Purpose, and Arguments of CLEAN Function
- Different Uses of CLEAN Function
- Removing Non-Printable Characters
- Removing Line Breaks
- Combining CLEAN and TRIM Function to Remove Extra Space from Text Strings
- Combining TRIM, CLEAN and SUBSTITUTE Functions to Remove Non-Breaking Spaces from Text Strings
- Replacing Characters using CLEAN Function
- Extract Left Side Texts Using the CLEAN Function with LEFT and FIND Functions
- How to Remove Non-Printable Characters using VBA Code
- How to Check If the Texts Need to be Cleaned or Not?
1. What is the CLEAN Function in Excel?
The CLEAN function is Microsoft Excel is categorized as an Excel text function that is used to remove non-printable characters from texts of a dataset. We frequently acquire data from many sources, and the CLEAN function can assist in eliminating the nonprintable characters from a specified text string. Additionally, it is helpful for erasing line breaks.
2. Syntax, Purpose, and Arguments of CLEAN Function
Syntax: =CLEAN(text)
Purpose: The purpose of CLEAN function is to remove non-printable characters from text strings.
Arguments: There is only one argument available in CLEAN function,
text- The text to clean non-printable characters from.
3. Different Uses of CLEAN Function
Excel’s clean function is a great tool for removing non-printable characters from text strings. In addition, there are several further uses for the CLEAN function. Let’s learn about some of the very helpful uses of CLEAN function by using this data set.
3.1 Removing Non-Printable Characters
In the given dataset, there are non-printable characters in the Product ID and Imported Email ID columns. Follow the steps below to learn how to remove them using CLEAN function.
Step 1: Select cell D2 and insert the CLEAN formula inside that cell.
Step 2: Press Enter key to see the result.
Step 3: Drag the fill-handle of D2 down to D6 to apply the formula in those cells too.
Step 4: Repeat the process in column E to clean the Email IDS.
3.2 Removing Line Breaks
Now we will remove line breaks from the Customer Names.
Step 1: Select cell C2 and type the CLEAN formula in it. Then press Enter key.
Step 2: Drag the fill-handle down to remove all the line breaks from customer names.
3.3 Combining CLEAN and TRIM Function to Remove Extra Space from Text Strings
In the Product ID column, there are some invisible non-printable characters named Extra Space which is =CHAR(13) along with the visible non-printable characters. The CLEAN function can remove these visible characters, but it can’t trace or remove the extra space. To remove this non-printable character, we will use TRIM function with CLEAN function.
Step 1: Insert the following TRIM and CLEAN combined formula in B2 and press Enter.
Step 2: Drag the cell downwards to clean all the product ids.
3.4 Combining TRIM, CLEAN and SUBSTITUTE Functions to Remove Non-Breaking Spaces from Text Strings
There are some Non-Breaking Spaces/=CHAR(160) in Imported Email IDS which are also invisible non-printable characters and can’t be removed by CLEAN function. We will combine CLEAN function with SUBSTITUTE and TRIM functions to remove this sort of non-printable character.
Step 1: Insert this combined formula in cell B2.
Step 2: Press Enter and drag the fill-handle down to apply the formula in the other cells of this column too.
3.5 Replacing Characters using CLEAN Function
What if we want to replace some texts from text strings? To change the “gmail” accounts to “outlook” accounts, for instance, we must make adjustments to the Imported Email ID column. Using the CLEAN and SUBSTITUTE functions together, we can complete both the task of replacing characters and the task of removing non-printable characters at once.
Step 1: Insert this formula shown in the below given picture inside B2 and press Enter key.
Step 2: Drag the cell down to B6 to remove non-printable characters as well as replace the gmail ids with outlook ids. Now look at B4 cell, the non-printable characters are removed but the email id did not change because of being a yahoo id. Follow the next step to change this into outlook id also.
Step 3: Select cell B4 and change the text “gmail” to “yahoo” inside the formula bar and press Enter.
3.6 Extract Left Side Texts Using the CLEAN Function with LEFT and FIND Functions
You can use the LEFT function with CLEAN and FIND functions to extract the name section or the left side texts of a given text from the Imported Email ID column.
Step 1: Select cell B2 and insert this combined formula inside this cell.
Step 2: Hit the Enter key and drag the fill-handle down to get the complete result.
4. How to Remove Non-Printable Characters using VBA Code
Step 1: Go to Developer tab and click on Visual Basic option.
Step 2: Click on Insert option and select Module.
Step 3: Insert this code inside VBA Module box and press the Run button or F5 key from your keyboard.
Sub cleanspace()
Range(“B2”) = Application.WorksheetFunction.Clean(Range(“A2”))
Range(“B3”) = Application.WorksheetFunction.Clean(Range(“A3”))
Range(“B4”) = Application.WorksheetFunction.Clean(Range(“A4”))
Range(“B5”) = Application.WorksheetFunction.Clean(Range(“A5”))
Range(“B6”) = Application.WorksheetFunction.Clean(Range(“A6”))
End Sub
Step 4: Go to your worksheet to see the result.
5. How to Check If the Texts Need to be Cleaned or Not?
Follow the steps below to check if the text strings on your worksheet needs to be cleaned or not.
Step 1: Insert this formula inside B2.
Step 2: Press Enter key from your keyboard.
Step 3: Drag the fill-handle downwards to see the complete result.
For ready-to-use Dashboard Templates:
