CLEAN Function Generator
CLEAN removes non-printable characters (like line breaks, tabs) from text. Pair with TRIM to fully sanitize data imported from external systems.
Syntax
=CLEAN(text)Try it — generate a CLEAN formula
Examples
Remove line breaks from cells
=CLEAN(A2)Strips CHAR(10) newlines copied from other apps.
Full sanitize
=TRIM(CLEAN(A2))CLEAN removes control chars, TRIM removes spaces.
Before VLOOKUP
=VLOOKUP(TRIM(CLEAN(A2)), Table, 2, FALSE)Ensures the key matches even if imported data has junk.
Tips
- ✓CLEAN only removes ASCII 0-31 chars. Use SUBSTITUTE for specific unicode chars.
- ✓Non-breaking space (CHAR(160)) is NOT removed by TRIM or CLEAN — use SUBSTITUTE.
- ✓Always pair with TRIM for external data cleanup.
Related Formulas
TRIM
TRIM removes leading and trailing spaces and collapses multiple internal spaces ...
SUBSTITUTE
SUBSTITUTE replaces occurrences of a specific text inside a string. Lets you tar...
VLOOKUP
VLOOKUP searches for a value in the first column of a table and returns a value ...
Need a different formula?
Describe any formula in plain English and FormulaPad will generate it.
Try FormulaPad Free