Function reference

CLEAN in Excel & Google Sheets

CLEAN is used in 1 formula pattern in this library. Browse worked examples below, or use the generator to build a custom variation for your specific data.

1 beginnerBuild a custom CLEAN formula →

When to use CLEAN

Fix exported CSV or database data that contains line breaks, tab characters, or other invisible characters that prevent VLOOKUP matches or cause display issues.

Worked examples (1)

Data Cleaningbeginner

Remove non-printable characters with CLEAN

=CLEAN(A2)

Open formula CLEAN reference

Common errors with CLEAN

Cell still looks broken after CLEAN

CLEAN only removes ASCII 0–31. Non-breaking spaces (ASCII 160) and other Unicode control characters require SUBSTITUTE(A2,CHAR(160),"") in addition.

Line breaks remain

Line breaks are CHAR(10) and are removed by CLEAN. If they persist, the character may be CHAR(13) — use SUBSTITUTE(A2,CHAR(13),"") explicitly.

Frequently asked questions

Should I use CLEAN and TRIM together?

Yes — =TRIM(CLEAN(A2)) is the standard combination. CLEAN removes invisible characters first, then TRIM removes any resulting extra spaces.

Need a custom variation?

Describe your specific CLEAN problem and get a formula written for your exact data.

Open generator with CLEAN