The Excel CLEAN function deletes the invisible troublemakers — non-printable control characters (ASCII codes 0–31) that ride along with data pasted from web pages, PDFs, and database exports. They don’t show on screen, but they break lookups, bloat LEN counts, and make “identical” cells refuse to match. CLEAN strips them in one move; pair it with TRIM and most imported text is fixed.
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text, or a reference to a cell, from which to remove non-printable characters. |
That’s the whole function: one argument. CLEAN removes the first 32 characters of the 7-bit ASCII table (codes 0–31) — things like line feeds (CHAR(10)), carriage returns (CHAR(13)), tabs (CHAR(9)), and the assorted control codes that printers and databases emit. Everything printable passes through untouched.
Available in: every version of Excel ever shipped — no compatibility worries, and formulas using it open cleanly for anyone.
Clean imported text
Column A below came from a system export. The ▮ marks show where invisible control characters hide — in Excel you’d see nothing, or a thin box. CLEAN removes them, and the LEN difference in column C proves characters were actually deleted:
| A | B | C | |
|---|---|---|---|
| 1 | Imported value | =CLEAN(A2) | =LEN(A2)-LEN(B2) |
| 2 | Acme▮Corp | AcmeCorp | 1 |
| 3 | Line1▮▮Line2 | Line1Line2 | 2 |
| 4 | Ready | Ready | 0 |
A quick diagnostic when two cells look identical but won’t match: compare lengths. If =LEN(A2) is bigger than the text you can see, something invisible is living in there.
Try it: interactive CLEAN demo
This sample cell is polluted with a tab, a line feed, and extra spaces. Toggle the formulas to watch each one clean a layer — hidden characters are shown as visible symbols so you can see what gets removed.
The full import-scrub: TRIM + CLEAN and beyond
CLEAN and TRIM split the cleanup job: CLEAN handles invisible control characters, TRIM handles visible-but-excess spaces. Imported data usually has both, so the standard scrub is:
One famous escapee: the non-breaking space, CHAR(160), which web pages use constantly. Its code is 160 — outside CLEAN’s 0–31 range — and TRIM ignores it too. SUBSTITUTE it into a normal space first:
Sometimes you don’t want to delete a character — you want to replace it. Line breaks inside a cell are CHAR(10); CLEAN would weld the lines together, but SUBSTITUTE can swap each break for a comma-space:
Cleaning a whole column? Put =TRIM(CLEAN(A2)) in a helper column, fill down, then copy and Paste Special › Values over the original. Done in four clicks.
Errors & common pitfalls
Pitfall: CLEAN doesn’t remove CHAR(160). The non-breaking space from web pages is character 160, outside the 0–31 range CLEAN targets. If web-pasted text still won’t match after TRIM(CLEAN(...)), this is almost always why. Fix: =TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).
Pitfall: CLEAN deletes line breaks you may want. In-cell line breaks are CHAR(10), squarely inside CLEAN’s kill zone — “Line1↵Line2” becomes “Line1Line2” with no space. To keep a separator, use =SUBSTITUTE(A2,CHAR(10)," ") instead.
Pitfall: CLEAN doesn’t touch spaces at all. Leading, trailing, doubled-up — ordinary spaces are printable (code 32), so CLEAN passes them straight through. Space problems are TRIM’s department; that’s why the two travel together.
Pitfall: the result is always text. Clean a cell containing the number 42 and you get the text string “42”. If downstream math needs a true number, wrap the result in VALUE: =VALUE(CLEAN(A2)).
#VALUE! from the cells you’re cleaning, not from CLEAN. CLEAN itself almost never errors — but if a lookup still fails after cleaning, check the other side too. The lookup table may carry the same invisible characters as the lookup value did.
Practice workbook
Frequently asked questions
What characters does CLEAN actually remove?
What's the difference between CLEAN and TRIM?
=TRIM(CLEAN(A2)).Why doesn't CLEAN fix my text pasted from a website?
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," "))).How do I find out if a cell contains hidden characters?
=LEN(A2)-LEN(CLEAN(A2)) counts non-printable characters, and =LEN(A2)-LEN(TRIM(A2)) counts excess spaces. Any result above zero means something invisible is in the cell.Does CLEAN remove line breaks inside a cell?
=SUBSTITUTE(A2,CHAR(10),", ").How do I clean an entire column at once?
=TRIM(CLEAN(A2)), fill it down, then copy the helper column and use Paste Special › Values over the original data. In Excel 365 one spilled formula does the whole column: =TRIM(CLEAN(A2:A500)).Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class