Syntax
CLEAN(text)
| Parameter | Description |
|---|---|
| text | Parameter of the CLEAN function. |
Examples
Clean imported data
Formula
=CLEAN(A1)
If A1 contains "John\x00Smith" (with a null character), returns "JohnSmith" with the invisible character removed.
Clean and trim combined
Formula
=TRIM(CLEAN(A1))
First removes non-printable characters, then removes extra spaces. The go-to combo for cleaning messy imported text.
Clean before matching
Formula
=VLOOKUP(CLEAN(A1), B:C, 2, FALSE)
Ensures the lookup value has no hidden characters that would prevent an exact match, fixing mysterious #N/A errors.
Common Errors
#VALUE!
Occurs if the argument is an error value rather than text. Wrap in IFERROR if the source cell might contain errors.
Tips
Doesn't remove all whitespace
CLEAN only strips ASCII 0-31. Non-breaking spaces (char 160) and other Unicode whitespace survive. Use SUBSTITUTE(CLEAN(A1), CHAR(160), " ") for those.
Combine with TRIM
Always pair CLEAN with TRIM: =TRIM(CLEAN(A1)). CLEAN removes control chars, TRIM removes extra spaces. Together they handle most import issues.
Line breaks are removed
CLEAN strips line feeds (char 10) and carriage returns (char 13). If you want to keep line breaks but remove other junk, use SUBSTITUTE instead.
Try CLEAN in Viztab
Import your data and use CLEAN with 370+ other formulas. No signup required.
Open Viztab