The CLEAN function with example in Excel

The CLEAN function in Excel is used to remove all non-printable characters from text. This can be particularly useful when dealing with data imported from other applications that might include characters that are not printable.

Syntax

excel
CLEAN(text)

• text: The text string from which you want to remove non-printable characters.

Example

Suppose you have the following text in cell A1 that includes non-printable characters:

Hello, World!

The character after the comma and before "World" is a non-printable character (ASCII code 127).

1. Basic Usage:
   • If you want to clean this text:
   • Formula: =CLEAN(A1)
   • Result: Hello, World!

Practical Example

Imagine you have a column of data with various non-printable characters, and you want to clean the entire column.

1. Enter the following text in cell A1 (including non-printable characters):

   arduino
   "Hello, World!"
   

2. In cell B1, enter the formula:

   excel
   =CLEAN(A1)
   

3. The result in cell B1 will be:

   Hello, World!
   

Step-by-Step Example

1. Input Data:

   • In cell A1, enter: Data with non-printable characters: Hello
   • The character after "characters:" and before "Hello" is a non-printable character (ASCII code 12, form feed).

2. Use the CLEAN Function:

   • In cell B1, enter the formula:

     excel
     =CLEAN(A1)
     

3. Result:

   • The result in cell B1 will be: Data with non-printable characters: Hello
   • The non-printable character is removed.

Combining CLEAN with Other Functions

You can also combine CLEAN with other functions like TRIM to remove both non-printable characters and extra spaces.

1. Input Data:

   • In cell A1, enter: Data with non-printable characters: Hello (note the extra spaces before and after the text).

2. Combine CLEAN and TRIM:

   • In cell B1, enter the formula:

     excel
     =TRIM(CLEAN(A1))
     

3. Result:

   • The result in cell B1 will be: Data with non-printable characters: Hello
   • Both the non-printable character and extra spaces are removed.

These examples illustrate how the CLEAN function can be used to sanitize text data in Excel, making it more suitable for further processing or analysis.