CLEAN Function in Excel – How to use it

In this tutorial let us explore how to use CLEAN Function in Excel with examples

Summary

CLEAN function is used to clean the characters which are not printable and removes the line breaks in the string.

The CLEAN function is an inbuilt worksheet function in excel which comes under the String/Text function category. This function could be used as a single function or nest it into other functions. This function could also be used in a macro (Excel VBA)

Syntax:

The syntax for the CLEAN function is

=CLEAN (text)

 

Arguments or Inputs

The CLEAN function requires the following arguments

text:

This argument is required

This is the string with unprintable characters like

Result:

The CLEAN function will return string after removing unprintable characters and extra line breaks. However, the Clean Function will only remove the first 32 characters of ASCII code (0 to 31). The table in the end of this tutorial lists the ASCII code 0 to31 characters.

Example

The following screenshot shows how the CLEAN function is used with different input values and the result of the function.

clean function screenshot

Explanation based on the above screenshot

 

  • Function is CLEAN(A2)

A2 is the reference to the text in cell A2

The input string has an unprintable character

The CLEAN function removes the unprintable character and returns the corrected string “Good Morning”

  • Function is CLEAN(A3)

A3 is the reference to the text in cell A3.

The input string has an unprintable character

The CLEAN function removes the unprintable character and returns the corrected string “HELLO WORLD”

  • Function is CLEAN(A4)

A4 is the reference to the text in cell A4.

The input string has an unprintable character

The CLEAN function removes the unprintable character and returns the corrected string “New moonday”

  • Function is CLEAN(A5)

A5 is the reference to the text in cell A5.

The input string has multiple line breaks

The CLEAN function removes all the line breaks and returns the cleaned string.

If the input text has more spaces between words, we could use the TRIM function along with CLEAN function

The formula to remove space and clean unprintable characters will be =TRIM(CLEAN(A5))

 

Note: Usually when we import data from an external source, we may get words with extra line breaks and unprintable characters. In such case CLEAN function along with other TEXT functions like TRIM, FIND, SUBSTITUTE, VALUE may come handy.

 

Question: Is there any other ways to clean data which comes with unprintable characters which cannot be cleaned by CLEAN Function

Answer: If the CLEAN function cannot clean the data for unprintable characters, we could use the spell check feature to clean the data. Also, we could use the Find and Replace dialog box to clean the data (Ctrl+H will open the Find and Replace dialog box). One more method is to create a macro which could clean all the special characters. There are a lot of third-party add-ins available in the net to clean data.

The CLEAN function is not available in VBA but we could use it with “worksheetfunction” prefix. The code will look like the one given below.

Cells(rw,4) = worksheetfunction.clean(cells(rw,3) )

rw is a variable defined for row numberlanation based on the above screenshot

  • Function is VALUE(A2)

A2 is the reference to the text in cell A2.

The number is stored as text with preceding zeros.

Result is 1234 a valid number.

  • Function is VALUE(A3)

A3 is the reference to the find text in cell A3.

The decimal is stored as text.

Result is 0.234 a valid decimal.

Note the result is aligned to the right of the cell. In Excel text is aligned to the left and number and dates are aligned to the right.

  • Function is VALUE(A4)

A4 is the reference to the find text in cell A4 which is a string.

The function returns a #value error.

  • Function is VALUE(A5)

A5 is the reference to the find text in cell A5.

The argument is a valid number.

The function returns the same number.

Question: Can we use value function in macro (Visual Basic for application)

Answer: This function is not available in macro and could not use it with “Application.worksheetfunction” prefix. However you could use the type conversion functions, cint and cdbl in VBA. Look at the below sample code.