CLEAN Function in Excel

In this post, let’s learn how the CLEAN function works in excel, describe the formula syntax, and how you can use the CLEAN function in your spreadsheet. 

What is CLEAN Function in Excel? 

CLEAN function is used in a spreadsheet to remove all non-printable characters from the text in a cell. 

Syntax of CLEAN Function 

CLEAN (text) 

Parameter

Text->The information that you want to remove nonprintable characters. 

Example: 

CLEAN(H7) 

How to use CLEAN function in Excel?

The following are the steps to use the CLEAN function in excel: 

Example 1: 

LINE BREAK 

• Create a spreadsheet with the required data in it. 

• Use the CLEAN function to remove the line breaks or nonprintable characters. 
•  Let’s see the below example, in the first cell the data is given as “Hi everyone. Welcome to developerpublish.com” with a line break. We use the =CLEAN(A1) formula to remove the line break.

Example 2:

Hidden characters 

• Within this example, we are going to use the CLEAN function to clean the hidden characters of the text. 
• In the below example, in the first cell the text “DEVELOPERPUBLISH” contains a hidden character within it as “=CHAR (9) &”DEVELOPERPUBLISH”&CHAR (10)”. 
• In the second cell(A2) using CLEAN(A1) formula cleaned version of the cell(A1) text presents. 
• To show the difference of the text in the cell A1 and A2 we have used LEN function (return the number of characters in a text string)  
• The number length of text in A1 and A2 differs. 

Note:

• We can also clean the weird characters using the clean function. 
• We should be careful when using other function in the formula because it takes input as a text value and returns as text.