How to Use CLEAN Function in Excel – 10 Examples

This is an overview.

CLEAN Function in Excel: Syntax

Function Objective

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

Syntax

=CLEAN(text)

Arguments

Return Value

The text string with no non-printable characters.

Note

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

This is the sample dataset.

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

The dataset showcases Email Ids with non-printable characters: CHAR(15), CHAR(12).

To remove them:

• Select the output cell D5.
• Enter the following formula.

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

• Press ENTER

• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Example 2 –Removing a Line Break with the CLEAN Function in Excel

You have a line break between the first name and the last name.

To remove it:

• Select the output cell D5.
• Enter the following formula.

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

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Example 3 – Using the CLEAN and the TRIM Functions to Remove Extra Spaces in Excel

You have extras space in the text strings.

Use the TRIM function with the CLEAN function to remove CHAR(15), CHAR(12) and CHAR(32) in the Imported Email Id column.

• Select the output cell D5.
• Enter the following formula.

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

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

In the Imported Email Id column, there are non-breaking spaces: CHAR(160).

To remove them, use the SUBSTITUTE function  with the CLEAN and the TRIM functions.

• Select the output cell D5.
• Enter the following formula.

C5 is the Imported Email Id from which you 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 extra spaces from the text string.

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

Count the number of characters in Student Names using the LEN function after removing the line breaks with the help of the CLEAN function.

• Select the output cell D5.
• Enter 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 extra spaces from the text string. LEN will count the character length.

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

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

To extract the name portion in the Imported Email Id, use the CLEAN function and the LEFT function.

• Select the output cell D5.
• Enter the following formula.

C5 is the Imported Email Id.

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

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Example 7 – Replacing Characters using the SUBSTITUTE and the CLEAN Functions in Excel

Create a New Email Id by removing the non-printable characters and replacing “gmail” with “yahoo” in the Imported Email Id column.

• Select the output cell D5.
• Enter the following formula.

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

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Example 8 – Checking If the Texts Need to be Cleaned

To find texts you need to clean, use the IF function.

• Select the output cell D5.
• Enter the following formula.

C5 is the Imported Email Id.

CLEAN(C5)=C5 is the logical test: the text string is equal to the text string, in which all non-printable characters were removed. When it is TRUE, IF will return “Cleaned”. Otherwise “Not Cleaned”

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

This is the output.

Example 9 – Getting the Average Values Using the CLEAN and the VALUE Functions

Remove non-printable characters using the CLEAN function and get the Average Marks without converting them into values using the VALUE function.

• Select the output cell D5.
• Enter the following formula.

C5 is the Imported Marks.

CLEAN will remove unnecessary data (but convert it to text) and VALUE will convert the text strings into numbers.

• Press ENTER
• Drag down the Fill Handle to see the result in the rest of the cells.

You will get the marks in number format.

To get the Average Marks, enter the following formula in D11.

D5:D10 is the Marks range.

73.83 is the Average Mark.

Example 10 – Using the CLEAN Function in Excel VBA to Remove Non-printable Characters

• Go to the Developer Tab>>Visual Basic.

In the Visual Basic Editor:

• Go to Insert >> Module

A Module will be created.

• Enter 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

Non-printable characters in C5:C10 will be removed and the output will be displayed in Column D.

• Press F5

This is the output.

Things to Remember

• This function only removes  0 to 31 7-bit ASCII code from the given text.
• The CLEAN function can also remove 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

<< Go Back to Excel Functions | Learn Excel