Excel CLEAN Function: Syntax, Examples and Tips

Q: Does the CLEAN function remove spaces?

No. CLEAN removes only characters with codes 0 to 31. Use TRIM for spaces and SUBSTITUTE for the non-breaking space CHAR(160).

Q: Does CLEAN remove line breaks in Excel?

Yes. Line feed (code 10) and carriage return (code 13) are both non-printable, so CLEAN deletes them and joins the lines.

Q: What is the difference between CLEAN and TRIM?

CLEAN deletes control characters; TRIM deletes leading, trailing and repeated spaces. Most imported data needs both, written as TRIM(CLEAN(text)).

Part of the free Module 5: Excel Formulas and Functions · Function 10 of 105 · Full Excel course

The Excel CLEAN function removes all non-printable characters with codes 0 to 31 from a text string and returns the cleaned text. Use it on data imported from databases, web pages or other applications where hidden control characters, tabs and line breaks stop lookups, sorting and calculations from working correctly.

Table of Contents

Syntax

=CLEAN(text)

Arguments

Argument	Required / Optional	Meaning
text	Required	The text, or a reference to a cell containing text, from which non-printable characters should be removed.

What CLEAN removes and what it keeps

CLEAN deletes the first 32 characters of the 7-bit ASCII table (codes 0 to 31). That includes tabs (code 9), line feeds (code 10) and carriage returns (code 13). It does not remove ordinary spaces, the DEL character (code 127) or the non-breaking space (code 160) that is common in text copied from websites. For those, combine CLEAN with TRIM and SUBSTITUTE.

Step-by-step example

Paste a product list exported from an ERP system into column A. Some cells look normal but a VLOOKUP against them returns #N/A.
In B2 enter =LEN(A2) and in C2 enter =LEN(CLEAN(A2)). If B2 is larger than C2, the cell contains hidden characters.
In D2 enter =CLEAN(A2) and fill down.
Copy column D and paste it as values over column A, then delete the helper columns. The lookups now match.

Practical use cases

1. Full clean-up of imported text

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

SUBSTITUTE converts non-breaking spaces to normal spaces, CLEAN removes control characters and TRIM collapses repeated spaces. This is the standard three-function clean-up formula.

2. Remove line breaks from multi-line cells

=CLEAN(A2)

Because line feeds are code 10, CLEAN joins the lines together. Add a separator first if you need one: =CLEAN(SUBSTITUTE(A2,CHAR(10)," | ")).

3. Fix numbers stored as text

=VALUE(TRIM(CLEAN(A2)))

Control characters attached to a number make Excel treat it as text. Cleaning it and wrapping in VALUE returns a real number that SUM can add.

4. Count cells that need cleaning

=SUMPRODUCT(--(LEN(A2:A500)<>LEN(CLEAN(A2:A500))))

Common mistakes and tips

Expecting CLEAN to remove spaces: it does not. Use TRIM for leading, trailing and doubled spaces.
Non-breaking spaces survive: CHAR(160) is outside the 0 to 31 range. Replace it with SUBSTITUTE as shown above.
Unicode control characters: characters such as zero-width spaces (code 8203) are not removed. Use SUBSTITUTE(A2,UNICHAR(8203),"").
Intentional line breaks disappear: if you want to keep line breaks, remove only the characters that cause problems instead of using CLEAN.
CLEAN never returns an error on text input; if you see #VALUE!, the error comes from a function nested inside it.

How to detect which characters need cleaning

Before applying CLEAN to a whole column, it helps to know what you are removing. Compare LEN(A2) with LEN(CLEAN(A2)); a difference confirms that control characters are present. To identify them, list each character code with =CODE(MID($A$2,ROW(A1),1)) filled down. Values from 1 to 31 are removed by CLEAN, 32 is a normal space, 160 is the non-breaking space that TRIM and CLEAN both ignore, and 127 is the DEL character that also survives CLEAN. In Excel 365 you can combine the test into one formula: =SUMPRODUCT(--(CODE(MID(A2,SEQUENCE(LEN(A2)),1))<32)) counts the control characters in a cell. Once you know the codes, build the shortest formula that fixes the actual problem instead of applying every cleaning function to every column, which slows down large workbooks.

Related functions

TRIM: removes extra spaces from text.
SUBSTITUTE: replaces specific characters, including CHAR(160).
CHAR: returns a character from its code, useful for targeting specific control characters.
LEN: counts characters, which helps you detect hidden ones.
See all lessons in the Excel Formulas course.