How to use the CLEAN function in Microsoft Excel

Let's look at some Excel Clean function examples and explore how to use the Clean function as a worksheet function in Microsoft Excel:

Summary

As financial analysts, we would often import data from various sources and the CLEAN function would help remove non-printable characters from THE supplied text string. It is also useful in removing line breaks.
The Clean function is a built-in function in Excel that is categorized as a Text Function.

Purpose

Strip non-printable characters from text

Return value

CLEAN function returns a text value.

What Does It Do ?

This function removes any non-printable characters from text.
These non-printable characters are often found in data which has been imported from other systems such as database imports from mainframes.

Syntax

CLEAN function has one syntax and hence they can be written in Two different ways:

=CLEAN(text) or
=CLEAN(cell reference)

Parameters or Arguments

Text: The text from which to clean.
Cell reference: Reference to cells containing text.

Formatting

No special formatting is needed.

Usage notes

• You can use CLEAN to remove characters that are not printable from text.
• You can also use CLEAN to strip line breaks from text.
• The Clean function cannot handle two characters: Character 127, which is a vertical rectangle, and character 160, which is a non-breaking space typically used for websites. If your data contains these characters, you need to use a different technique. The Substitute and/or Trim function can remove these characters.
• The CLEAN function removes the first 32 (non-printable) characters in the 7-bit ASCII code (values 0 through 31) from text.
• In the Unicode character set, there are additional non-printable characters (values 127, 129, 141, 143, 144, and 157). By itself, the CLEAN function does not remove these additional non-printable characters.
• The function was introduced in Excel 2000 and is available in all versions after that.

Below are the list of all non-printable character

Click here to learn more excel function