CLEAN

The CLEAN function in Excel is used to remove non-printable characters from a text string. It comes in handy when dealing with text data that may contain non-printable characters, such as extra spaces, line breaks, or other non-visible characters.

Syntax

=CLEAN(text)

text The text string from which non-printable characters need to be removed.

About CLEAN

When working with textual data, it's not uncommon to encounter hidden non-printable characters that can disrupt the formatting and functionality of your content. This is where the CLEAN function swoops in to save the day. It effortlessly purges text strings of any non-printable characters, ensuring their cleanliness and readability in your Excel sheets. This can be particularly useful when importing data from external sources or dealing with user-generated inputs where the presence of non-printable characters may go unnoticed by the naked eye, yet still have undesired effects on the data integrity and visual presentation within your workbooks. By applying CLEAN to the afflicted text, you efficiently declutter it of any invisible impediments, leaving you with a pristine and well-behaved string that plays nice with your Excel formulas and functions.

Examples

Let's say you have a text string containing non-printable characters, such as extra spaces and line breaks. You can use the CLEAN function to clean up the text. For instance, if cell A1 contains the problematic text, you can use the formula =CLEAN(A1) in another cell to obtain the cleaned version of the text.

Suppose you have imported a dataset from a text file, and some of the cells contain non-printable characters. You can use the CLEAN function to normalize the text in those cells and ensure consistency and cleanliness across your dataset.

Questions

What type of non-printable characters does the CLEAN function remove?

The CLEAN function removes non-printable ASCII characters with ASCII codes 0 to 31 from the specified text string.

When is it useful to use the CLEAN function in Excel?

The CLEAN function is useful when working with text data that may contain non-printable characters, such as extra spaces, line breaks, or other non-visible characters that could affect the visual presentation or functionality of the data.

Does the CLEAN function remove non-printable Unicode characters?

No, the CLEAN function only removes non-printable ASCII characters with ASCII codes 0 to 31. It does not remove non-printable Unicode characters beyond this range.

Related functions

TRIM
SUBSTITUTE
TEXT
MID
LEFT
RIGHT