How CLEAN works in Excel? Best CLEAN examples

When working with Excel, we often deal with messy data. Unwanted non-printable characters can sneak into our spreadsheets, causing all sorts of issues. That’s where the CLEAN function comes in. It’s a powerful tool to help tidy up data and ensure that only standard printable characters remain.

What is the CLEAN function in Excel?

The CLEAN function in Excel is designed to remove non-printable characters from text. These characters typically appear when you import data from external sources such as databases, web pages, or other applications. Many of these characters are invisible but can still interfere with your formulas, reports, and overall data presentation.

Here’s the syntax for the CLEAN function:

=CLEAN(text)

The text argument represents the cell or string from which you want to remove non-printable characters. After applying the function, Excel returns the cleaned text with only the printable characters remaining.

How CLEAN works in Excel? Best CLEAN examples

Let’s go through some practical examples to better understand how CLEAN works in different scenarios.

Example 1: Removing non-printable characters from imported data

Imagine you have a dataset imported from a different system, and some cells contain hidden, non-printable characters. Here’s an example:

Original Data	After CLEAN Function
`Product#1` (with extra hidden characters)	`=CLEAN(A2)`
`Item A` (line break inside)	`=CLEAN(A3)`

Example 2: Handling line breaks in text

Sometimes, non-printable characters include unwanted line breaks. Let’s say you have a multi-line entry like this:

Item 1
Item 2
Item 3

And this data is stored in A1. Applying the CLEAN function like this:

=CLEAN(A1)

will remove those line breaks, converting the data into:

Item 1Item 2Item 3

While this function removes unwanted breaks, if you want spaces instead of direct concatenation, you might need to combine CLEAN with other functions like SUBSTITUTE.

Example 3: Cleaning up copied web data

Data copied from the web or external databases often includes special characters that don’t display properly in Excel. Applying CLEAN helps in eliminating those characters, making sure your text stays uniform and readable.

Limitations of the CLEAN function

Despite its usefulness, the CLEAN function has certain limitations:

It only removes the first 32 non-printable ASCII characters (0 to 31).
It does not remove all special characters, like non-breaking spaces (CHAR(160)).
It does not fix unnecessary spaces within text; for that, you need the TRIM function.

If you also need to remove non-breaking spaces, you can use:

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

Combining CLEAN with other functions

To enhance the power of CLEAN, you can combine it with other functions like:

1. TRIM – To remove extra spaces:

=TRIM(CLEAN(A1))

1. SUBSTITUTE – To replace unwanted characters:

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

1. TEXTJOIN – To concatenate cleaned and structured data:

=TEXTJOIN(", ", TRUE, CLEAN(A1), CLEAN(A2), CLEAN(A3))

Final thoughts

The CLEAN function is an essential tool when working with imported, copied, or messy data in Excel. While it has certain limitations, combining it with functions like TRIM and SUBSTITUTE can significantly improve your data processing workflow. If you’re struggling with unexpected formatting issues in your dataset, applying the CLEAN function is often the perfect first step.

Other interesting article:

How TRIM works in Excel? Best TRIM examples