CLEAN Function

Summary

The Excel CLEAN function removes non-printable characters from text. These hidden characters often appear in imported data and can interfere with lookups, comparisons, and formatting.

CLEAN removes characters with ASCII codes 0 to 31. It does not remove normal spaces, so it is often paired with TRIM. A common pattern is =TRIM(CLEAN(A1)).

This usually matters during import cleanup. Data copied from websites, PDFs, system exports, or older software can carry control characters that break matching and sorting even though the visible text still looks normal at first glance.

CLEAN is often most effective as the first cleanup step. After it removes hidden non-printing characters, functions like TRIM or SUBSTITUTE can handle spacing or specific symbols that still need attention.

Syntax

=CLEAN(text)

CLEAN takes one argument: the text or cell you want to clean. Excel checks the full string and removes non-printable characters wherever they appear.

Arguments

• text - The text string or cell reference to clean.

CLEAN vs Other Functions

CLEAN and TRIM are often used together, but they solve different problems. CLEAN removes hidden control characters. TRIM removes extra visible spaces. If web data contains a non-breaking space, you may also need SUBSTITUTE.

One important limitation is that CLEAN does not remove the non-breaking space, often written as CHAR(160). That character is common in copied web data and looks like a normal space. If it remains, use =SUBSTITUTE(A1,CHAR(160),"").

Using CLEAN

CLEAN is most useful when text looks fine on screen but behaves strangely in formulas. Imported rows from PDFs, websites, and older systems often contain hidden characters that prevent exact matches or create unexpected line breaks.

In practice, CLEAN is often used in a helper column together with TRIM. If you want to confirm that hidden characters were removed, compare LEN(original) with LEN(CLEAN(original)).

• Use TRIM(CLEAN(A1)) as a standard cleanup pattern for imported text.
• Use SUBSTITUTE if the problem is a non-breaking space rather than a control character.
• Use LEN before and after CLEAN when you want proof that something was removed.

Example 1 - Removing a Line Break from Imported Text

A line break inside a cell often makes the row look taller than expected. CLEAN removes that hidden character and returns a flat text string.

=CLEAN(A1)   // Removes CHAR(10) from "Product
Name"
// Result: "ProductName"

Example 2 - Fixing Text from an Old System

Older exports sometimes contain control characters that are invisible in the cell but still part of the value. CLEAN removes them without needing a separate rule for each code.

=CLEAN(B2)   // Input: "Part" + CHAR(7) + "001"
// Result: "Part001"

Example 3 - Cleaning and Trimming in One Step

Many imported values contain both hidden characters and extra spaces. Wrapping CLEAN inside TRIM handles both problems in one formula.

=TRIM(CLEAN(C3))    // Removes hidden chars and extra spaces
=PROPER(TRIM(CLEAN(A1))) // Can also be combined with other text functions

Example 4 - Measuring Characters Before and After

Using LEN with CLEAN is a quick way to confirm that hidden characters were removed. If the count gets smaller, the original text contained non-printable characters.

=LEN(D4)           // Count before cleaning
=LEN(CLEAN(D4))    // Count after cleaning
// The difference shows how many hidden characters were removed

CLEAN checks the entire text string, not just the first character. If hidden characters appear at the beginning, middle, or end, CLEAN removes them all.

• CLEAN removes ASCII control characters 0 to 31.
• Use TRIM for extra spaces and SUBSTITUTE for CHAR(160).
• Use LEN if you want to verify the cleanup result.

Conclusion Recap

CLEAN is the function to use when text looks normal on screen but still behaves strangely in formulas. This lesson showed that the problem is often hidden control characters from imports, and CLEAN removes those without changing the visible words themselves.

The most useful habit is to pair CLEAN with the right helper. Use TRIM when spaces are also messy, and use LEN if you want to confirm that something was removed. CLEAN is simple, but it can fix a lot of frustrating text problems fast.

• Summary: CLEAN removes non-printable characters from text.
• Syntax: =CLEAN(text).
• Best partner: TRIM, because spaces and hidden characters often appear together.
• Limitation: CLEAN does not remove non-breaking spaces such as CHAR(160).
• Typical use: Fixing imported text before lookups, comparisons, and reporting.