CLEAN Function in Microsoft Excel: Examples, Pitfalls & Compatibility
CLEAN in Microsoft Excel: remove ASCII control characters from imported text without pretending to remove every invisible character. Includes worked examples and application-specific cautions.
Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in Microsoft Excel. How formula examples are checked
Quick answer: Use CLEAN in Microsoft Excel to remove ASCII control characters from imported text without pretending to remove every invisible character. The official CLEAN reference is the source for the function definition and platform-specific rules described below.
Syntax and arguments
=CLEAN(text)
text (required): The text or one cell containing the characters to inspect.
Examples use English function names, comma argument separators and decimal points. Adapt formula separators to your workbook settings, not punctuation inside quoted text.
When to use it
Use CLEAN when imported descriptions contain tabs or line breaks that should disappear. Do not use it as a general spelling, whitespace or Unicode repair tool. Removing a separator is a different decision from replacing that separator with a space.
Three worked examples
Each example is independent. Where input cells are listed, enter those values first; otherwise the formula is self-contained. Expected outputs are independently checked calculations or documented error expectations, not captured results from Microsoft Excel.
Example 1: Remove a tab from a lot code
=CLEAN("Lot"&CHAR(9)&"42")
Expected result: "Lot42" (quoted here to make spaces and text boundaries visible).
The tab is deleted, leaving the two visible fragments adjacent. No space is added.
Example 2: Retain ordinary spaces
=CLEAN(" A "&CHAR(10)&"B ")
Expected result: " A B " (quoted here to make spaces and text boundaries visible).
The line feed disappears, but the leading, internal and trailing ordinary spaces survive. The expected text includes a space at each end.
Example 3: Combine deliberate cleaning and trimming
=TRIM(CLEAN(" Lot "&CHAR(9)&"42 "))
Expected result: "Lot 42" (quoted here to make spaces and text boundaries visible).
CLEAN first removes the tab. TRIM then removes the outer spaces while preserving one ordinary separator between Lot and 42.
Put it to work safely
Keep an untouched input column and place the cleaned result beside it. Compare LEN on the original and result to see whether any characters were removed. Review a few changed rows before pasting values over the source. A shorter string is not automatically a better identifier: deleting the only separator between two words can accidentally create a new word or code.
Common mistakes and fixes
Words become joined. CLEAN removes the control character; it does not insert a replacement space. Where a tab separates two fields, use SUBSTITUTE to replace that known tab with a space before trimming.
Visible spaces remain. Ordinary spaces are not the ASCII controls this function targets. Apply TRIM only when collapsing repeated ordinary spaces is appropriate.
Two cleaned keys still differ. A nonbreaking space or another Unicode character may remain. Inspect the character code rather than repeatedly applying CLEAN.
Microsoft Excel-specific notes
Microsoft describes the removed set as ASCII codes 0 through 31 and identifies additional control characters that CLEAN does not remove. A Windows code-page assumption should not be used to identify every imported Unicode character. See the vendor reference for the current definition.
Excel and Google Sheets comparison
Both help pages document the ASCII-cleaning purpose. This does not make CLEAN a Unicode normalizer in either application. When exchanging identifier columns, compare the resulting strings rather than judging only their appearance.
Compare the CLEAN guide for Google Sheets and its official documentation. Having a guide in both libraries establishes two documented references, not a certificate of identical behavior across releases, locales or file conversions.
Related guides
Evidence and testing scope
Editorial verification: Official references retrieved on 2026-10-12. The three worked cases are included in this release’s reproducible Python checking suite. It checks the stated arithmetic, calendar or text model and the expected documented error classification; it is not the Microsoft Excel calculation engine. No native application run, UI screenshot, maximum-size benchmark or cross-version certification is claimed. Review the editorial testing standards before relying on a formula in a critical workbook.