CLEAN
Removes all non-printable characters from text.
CLEAN(text)Arguments
textText to clean
=CLEAN(A1)Removes control characters
=TRIM(CLEAN(A1))Removes non-printable and extra spaces
=CLEAN(CHAR(7)&"Hello")Removes bell character
- •Removes ASCII 0-31 characters
- •Often combined with TRIM
- •Useful for imported data cleanup
- •Expecting CLEAN to remove regular spaces - it only strips non-printable control characters (like line breaks pasted from other software), and TRIM is needed separately for ordinary extra spaces
- •Assuming CLEAN handles every possible invisible character, when some Unicode characters (like non-breaking spaces, character code 160) aren't classified as non-printable and pass through CLEAN untouched
- •Applying CLEAN as a first step without also applying TRIM afterward, leaving irregular spacing behind even though the control characters themselves are gone
TRIMRemoves extra regular spaces, complementing CLEAN's removal of non-printable control characters - the two are frequently used together.SUBSTITUTECan target specific problem characters CLEAN doesn't catch, like a non-breaking space (CHAR(160)).CODECan help identify exactly which invisible character is causing a problem before deciding whether CLEAN, TRIM, or SUBSTITUTE is the right tool.Does CLEAN remove extra spaces between words?
No, CLEAN only removes non-printable control characters, not regular spaces. Combine it with TRIM, like TRIM(CLEAN(A1)), to handle both problems at once.
Why does CLEAN not fix text pasted from a website?
Some invisible characters, like non-breaking spaces, aren't classified as non-printable control characters, so CLEAN leaves them untouched. SUBSTITUTE with CHAR(160) can target that specific character directly.
What kind of characters does CLEAN actually remove?
The first 32 characters in the ASCII set - things like line feeds, carriage returns, and tabs that often come along when pasting text from other applications.
Need to translate a formula using CLEAN?
Use our translator to convert your complete formula