CLEAN Function in Excel

The CLEAN function in Excel removes all nonprintable characters from text. The CLEAN function in Excel is a part of text formulas.

When we import the data from different sources such as XML database or web, it may contain different nonprintable characters and its necessary to remove these characters. Sometimes the nonprintable characters are not visible, these still need to be removed.

The Excel CLEAN function removes the nonprintable characters and line breaks from the text which is imported from other applications.

How to use CLEAN Function in Excel

The article in this webpage describes how to use the Excel CLEAN function with syntax, arguments, detailed explanation, and examples step by step.

CLEAN Syntax

=CLEAN(text)

The argument of CLEAN function is:

• text (required) – The data from which you want to remove the nonprintable characters.

CLEAN Examples

Let’s understand the CLEAN function in Excel with examples.

1. Remove Nonprintable Characters

The primary purpose of the CLEAN function is to remove unwanted characters from a text string which will not print such as line breaks, tabs, and other control characters. These characters are typically invisible and can cause issues in data processing or formatting.

For example, in cell A2 there is unwanted character at the start and end of the text. Use the CLEAN function in cell C2 to remove the non-printable characters.

=CLEAN(A2)

Copy the formula from cell C2 to C3 to remove the unwanted characters in cell A3.

2. Remove Line Breaks

The line breaks are often a common issue in text imported into Excel from other source or online. The Excel CLEAN function is used to remove the line breaks within the cell.

In this example, there are line breaks after the text “Monthly” and “Sales”, due to which it shown in 3 lines. To remove the line breaks and keep the text in one line, the formula in cell C2 will be:

=CLEAN(A2)

The CLEAN formula will remove all the line breaks in cell A2, and the result will be in cell C2.

3. Combine the CLEAN and TRIM function in Excel

The CLEAN function removes the line breaks but will not remove the extra spaces. For example, there are line breaks and extra spaces before “Sales” as well as “Reports” in cell A2. The CLEAN function in cell C2 will remove the line break, but you can find extra spaces between the words.

You can combine the TRIM and CLEAN function to remove the lines and spaces at the time. The formula in cell C2 will be:

=TRIM(CLEAN(A2))

The TRIM function will remove the extra spaces before or after the text in each line.

4. Combine the CLEAN and SUBSTITUTE function in Excel

The CLEAN formula in Excel will remove all the non-printable characters, but it will not remove the special characters. In this CLEAN function example, the formula in cell C2 will not remove the special character in cell A2.

To remove the special characters and non-printable characters, you can combine the SUBSTITUTE and CLEAN function in Microsoft Excel.

=SUBSTITUTE(CLEAN(A2),”‡”,””)

In this example, the SUBSTTUTE function will replace the special character (“‡”) with none/blank (“”). 

Important Notes

• The CLEAN function introduced in Excel 2000 and is supported in all subsequent Excel versions.
• The CLEAN formula removes the first 32 non-printable characters in the 7-bit ASCII code (values 0 to 31). The text without these characters is returned unchanged. The Unicode contains other non-printable characters and that cannot be removed by using the CLEAN formula.
• To remove specific characters apart from the first 32 ASCII characters, you can use the SUBSTITUTE, REPLACE and CHAR functions.

Learn More