CLEAN() Function in Excel: The Best Way to Remove Non-Printable Characters

Have you ever imported data into Excel and noticed strange, hidden characters messing up your work? These are non-printable characters that cause formatting issues and even ruin calculations.  

You can remove such unwanted characters with the CLEAN() function. In this article, I’ll walk you through the CLEAN() function, when to use it, and how it can make your work easier.

Quick Answer: The Excel CLEAN() Function

Here’s how to use the CLEAN function 

• Type =CLEAN(.

• Enter either the text you want to clean or the cell reference.

• Close the function with ).

• Press Enter.

=CLEAN(text)

text can be any string you want to clean.

=CLEAN(A1)

A1 can be replaced with a different cell reference.

An Example of the CLEAN() Function in Action

Let’s understand how we can use the CLEAN() function in a little more detail. This time, I'll include some screenshots.

Here, I have data in cell A2 with some unwanted characters. So, I enter the following formula in cell B2 to clean up my text:

=CLEAN(A2)

Use the CLEAN() function in a single cell. Image by Author.

Besides cleaning a single cell, we can even use the CLEAN() function to clean multiple cells simultaneously. All you have to do is, after writing the formula in the appropriate cell, drag the small green square at the corner of the cell to the last row. This way, the formula will be applied to the entire dataset, and you’ll have proper data within seconds. 

Clean multiple cells with the CLEAN() function. Image by Author.

I find the CLEAN() function especially useful after importing data from external sources such as databases or web pages. This is because, when you import data, non-printable characters with ASCII codes 0–31 may come along. Although we may not be able to see them, they can mess up our calculations or make our data look strange. 

Excel’s CLEAN() function removes these unwanted characters. It even cleans up unnecessary line breaks and other hidden characters that may cause formatting issues. So sometimes it’s helpful to use CLEAN() even if you don’t see any obvious issues.

Excel CLEAN() vs. TRIM()

If you have taken one of our courses, like Excel Fundamentals, you might be familiar with another common Excel function, the TRIM() function.

Although the CLEAN() and TRIM() functions both tidy up text, they work differently. CLEAN() removes non-printable characters (ASCII 0-31), and TRIM() removes extra spaces (ASCII 32) before, after, or between words (except, conveniently, a single space between the words). 

In other words, we can use CLEAN() to clean up data with hidden formatting issues, but if our data has inconsistent spacing, we can fix this with the TRIM() function.

Later on, I’ll show you how to combine CLEAN() with TRIM() in order to both remove non-printable characters and eliminate extra spaces.

Combining CLEAN() with Other Excel Functions 

Now that you know what the CLEAN() function is, let’s see where we can use this in real life:

Combining CLEAN() with TRIM() 

If you notice in the above example, CLEAN() didn’t remove the weird spacing between text. To fix this, I wrap it inside the TRIM() function and you can see we now have properly formatted data in column B:

=TRIM(CLEAN(A2))

Combine TRIM() and CLEAN() functions. Image by Author.

Using CLEAN() with SUBSTITUTE()

There are a few non-printable uni-code characters other than the first 32 ASCII characters, and CLEAN() alone cannot handle them. But if we combine the SUBSTITUTE() function with CLEAN(), we can remove them easily. Let’s understand this with an example:

I have a random text in cell A2 where we have a spacing issue on the left side of the text. But when I tried removing it with TRIM(), it didn’t go. This means it looks like space but actually it’s not — instead it’s a special character. In such cases, first identify the character code with the following formula:

=CODE(LEFT(A2,1))

This gives the character code, which is 160. You can now remove it with the following formula:

=SUBSTITUTE(CLEAN(A2),CHAR(160),"")

For a cleaner text, wrap the above formula inside TRIM().

=TRIM(SUBSTITUTE(CLEAN(A2),CHAR(160),""))

Clean cells using CLEAN() and SUBSTITUTE(). Image by Author.

Using CLEAN() with REPLACE()

If we want to replace any other delimiter with non-printable characters, we can combine the CLEAN() function with the REPLACE() function. The REPLACE() function in Excel replaces part of a text with new text based on a specified starting position and length. 

=REPLACE(old_text, start_num, num_chars, new_text)

For example, if I want to replace the -  sign with space and remove all non-printable characters, I will use the following formula:

=REPLACE(CLEAN(A2), 4, 1, " ")

This formula first cleans the old text and replaces - at position 4 with a space.

Clean text with REPLACE() and CLEAN() functions. Image by Author.

Using CLEAN() with REPT()

We can also combine the REPT() function with CLEAN() to clean up the text. The REPT() function repeats a text a specified number of times. 

=REPT(text, number_of_times)

For example, I apply the following formula to my dataset: 

=TRIM(CLEAN(SUBSTITUTE(A2, " ", REPT(" ", 2))))

Here, the SUBSTITUTE() replaces every single space with two spaces using REPT(" ", 2), making spacing more uniform. Then, CLEAN() removes hidden non-printable characters, and TRIM() removes extra spaces, leaving just one space between words.

Clean text using REPT() and CLEAN(). Image by Author.

As you can see, CLEAN() shows up many places and works well with many other functions.

A Couple of Things to Consider

While CLEAN() and SUBSTITUTE() are great for removing unwanted characters, sometimes we may end up removing something important if we do not pay attention. For example: 

• Accidental Data Loss: Avoid using formulas that are too broad. If we replace all spaces with nothing, the words can get merged. And if we remove all special characters, our formula may delete hyphens from phone numbers or decimal points from prices. So, before applying any function, check if the formula works appropriately. 
• Test on Subsets: To be safe, I always test my formula on a small dataset first. Instead of applying it to thousands of rows all at once, I try it on a few and check if it works as I expect. If everything looks good, only then do I apply it to the entire dataset.

Final Thoughts

The CLEAN() function quickly cleans text data by removing non-printable characters. When used alone, it ensures clean and structured data. When combined with the TRIM() function, it improves readability by also eliminating unnecessary spaces.

If you want to learn more Excel tips and tricks, we have some great courses to help you. Start with the Excel Fundamentals skill track to learn the basics, then move on to Data Analysis in Excel and Data Preparation in Excel course to hone your skills further.