CLEAN Function: Remove Non‑Printable Characters & Invisible Garbage

Home » pub » Tutorials » CLEAN Function: Remove Non‑Printable Characters & Invisible Garbage

Invisible characters break formulas silently. You import data from a database or web scrape, everything looks fine — until VLOOKUP returns #N/A on values that are clearly present, and COUNTIF counts zero for strings that visibly match. The culprit is almost always non-printable characters hiding inside the cells. Excel’s CLEAN function removes them in one step, stripping ASCII codes 0 through 31 that TRIM cannot touch. This guide covers 8 practical examples: basic cleaning, the standard TRIM+CLEAN pipeline, fixing non-breaking spaces with SUBSTITUTE, removing line breaks without merging words, diagnosing dirty data with CODE and LEN, and the master four-layer cleanup formula that handles every common invisible garbage type in a single formula.

Invisible characters break formulas silently. You import data from a database, a web scrape, or a legacy system, and everything looks fine — until VLOOKUP returns #N/A on values that are clearly there, COUNTIF counts zero for strings that visibly match, and numbers refuse to calculate. The culprit is almost always non-printable characters hiding inside the cells. The CLEAN function removes them in one step, stripping ASCII control characters that TRIM cannot touch.

CLEAN works on ASCII codes 0 through 31 — tab characters, carriage returns, line feeds, and other invisible control codes that database exports and web-pasted content routinely inject into cells. Furthermore, combining CLEAN with TRIM and SUBSTITUTE covers virtually every invisible garbage scenario you will encounter in real-world data work.

Availability: CLEAN works in every Excel version from Excel 2000 onwards, including Microsoft 365, Excel 2024, 2021, 2019, and 2016. It also works in Google Sheets and LibreOffice Calc with identical syntax.

What Is the CLEAN Syntax?

CLEAN accepts a single argument — the text to clean.

=CLEAN(text)

Argument	Required?	What it does
text	Required	The text string to clean. Can be a cell reference, a hardcoded string in double quotes, or a formula that returns text. CLEAN scans the string and removes any character whose ASCII code falls between 0 and 31 (inclusive). All other characters are preserved unchanged.

What Characters Does CLEAN Remove?

CLEAN removes the first 32 characters in the 7-bit ASCII table — codes 0 through 31. These are control characters from the original computing era. Most are invisible on screen. Consequently, they are easy to miss visually but cause significant problems in formulas and data comparisons.

ASCII Code	Character name	Removed by CLEAN?	Common source
0	Null character	✅ Yes	Database NULL values imported as text
9	Horizontal tab	✅ Yes	TSV files, copied from spreadsheet cells
10	Line feed (LF)	✅ Yes	Unix line breaks, Excel cell line breaks (Alt+Enter)
13	Carriage return (CR)	✅ Yes	Windows line breaks (CR+LF), older system exports
1–8, 11–12, 14–31	Other control chars	✅ Yes	Legacy system exports, PDFs, web scraping
32	Regular space	❌ No — use TRIM	Spaces between words, leading/trailing spaces
127	Delete character	❌ No — use SUBSTITUTE	Specific legacy systems
160	Non-breaking space	❌ No — use SUBSTITUTE	Web-pasted content, HTML

CLEAN does not remove spaces or non-breaking spaces: CLEAN stops at ASCII code 31. Regular spaces (code 32) require TRIM. Non-breaking spaces (code 160) — common in content pasted from web pages — require SUBSTITUTE(text, CHAR(160), ""). Combining all three functions covers the full range of invisible data garbage.

How Is CLEAN Different from TRIM?

CLEAN and TRIM solve different but related problems. They are frequently confused because both deal with invisible content. However, each one targets a completely different category of character.

Function	What it removes	What it does NOT remove
CLEAN	Non-printable control characters (ASCII 0–31) — line feeds, carriage returns, tabs, null characters	Spaces (ASCII 32), non-breaking spaces (ASCII 160), any character above code 31
TRIM	Leading spaces, trailing spaces, and runs of multiple spaces between words (reduces to one space each)	Non-printable characters, non-breaking spaces — TRIM is strictly space-only
CLEAN + TRIM	Everything above: control characters AND excess spaces — the standard starting formula for any data clean-up	Non-breaking spaces (code 160) — add SUBSTITUTE for complete coverage

Examples 1–4: Core Cleaning Tasks

Basic CLEAN — remove non-printable characters from imported text

Database exports and CSV files routinely embed control characters inside text fields. CLEAN removes them all in a single formula. The cleaned result looks identical to the original on screen — but now formulas, lookups, and comparisons work correctly.

A — Imported (messy)

B — CLEAN result

LEN difference

James[CHAR9]Smith

James Smith

LEN 12 → 11

Report[CHAR10]2026

Report2026

LEN 11 → 10

Basic CLEAN — removes ASCII 0–31 from the text in A2: =CLEAN(A2) Confirm that a cell is already clean — compare LEN before and after: If the lengths match, there are no hidden characters to remove. LEN(A2) = LEN(=CLEAN(A2)) → TRUE means the cell is clean → FALSE means CLEAN removed at least one hidden character To keep results as static values (so you can delete the original column): 1. Copy column B (the CLEAN results). 2. Paste Special → Values only (Ctrl+Shift+V → Values → Enter). 3. Delete column A.

TRIM + CLEAN — the standard data pipeline for imported text

CLEAN removes non-printable characters. TRIM removes extra spaces. Together they handle the full range of invisible damage that database exports and web-pasted data routinely contain. This combination is the standard starting formula for any imported text column. Apply CLEAN first, then wrap with TRIM.

Apply CLEAN first, then TRIM. CLEAN runs on the raw text, removing control characters. TRIM then removes any leading, trailing, or extra internal spaces. =TRIM(=CLEAN(A2)) → Removes non-printable characters AND normalises all spaces in one step Why TRIM wraps CLEAN (not the other way around): CLEAN may leave leftover spaces where control characters sat between words. TRIM then collapses those spaces to single spaces — in the right order. Audit which rows still had hidden characters after cleaning: Returns "Was dirty" for rows where CLEAN changed the content. =IF(TRIM(=CLEAN(A2))<>TRIM(A2), "Was dirty", "Already clean")

Use TRIM(CLEAN(...)) as your default starting formula: Apply it to every imported text column before any other transformation. Running CLEAN and TRIM first prevents invisible characters from propagating into lookup tables, PivotTables, and downstream reports.

Non-breaking spaces — CLEAN + SUBSTITUTE for web-pasted data

Non-breaking spaces (CHAR(160)) are the most stubborn invisible character in Excel. They appear when you paste content from websites, HTML tables, or PDFs. They look like regular spaces on screen, but TRIM ignores them because they are code 160 — above CLEAN's range. Consequently, SUBSTITUTE must handle them explicitly.

A — Web-pasted

After TRIM only

After full fix

[nbsp]£48,500[nbsp]

[nbsp]£48,500[nbsp] ✗

£48,500 ✓

Step 1 — Diagnose if a cell contains a non-breaking space. CODE returns the character code of the first character. If CODE returns 160, the cell starts with a non-breaking space. CODE(LEFT(A2, 1)) → 160 = non-breaking space present → Any other code = different first character Step 2 — Remove non-breaking spaces with SUBSTITUTE. Replace every CHAR(160) occurrence with a normal space, then TRIM. =TRIM(SUBSTITUTE(A2, CHAR(160), " ")) → Removes all non-breaking spaces and trims the result clean Full three-function pipeline — handles ALL common invisible garbage: SUBSTITUTE removes CHAR(160) first. CLEAN then removes ASCII 0–31 characters. TRIM finally normalises all spaces. =TRIM(=CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))

Remove line breaks from cells — CLEAN strips Alt+Enter characters

Excel's Alt+Enter creates a line break inside a cell using CHAR(10) — a line feed character. These appear as visible line breaks in Wrap Text mode. However, they break VLOOKUP, EXACT, and text comparisons because the cell value contains a hidden character. CLEAN removes them instantly.

Remove line breaks (CHAR 10) from a cell — CLEAN strips them automatically: Alt+Enter in Excel inserts CHAR(10). CLEAN removes CHAR(10) as part of 0–31. =CLEAN(A2) → "John Smith" → "JohnSmith" (words join without a space) Note: CLEAN removes the line break but does not add a space in its place. If the line break was between words, add SUBSTITUTE to replace CHAR(10) with a space first, then CLEAN handles any other control characters, then TRIM normalises spaces. =TRIM(=CLEAN(SUBSTITUTE(A2, CHAR(10), " "))) → "John Smith" → "John Smith" (correct — space preserved between names) Handle both CHAR(10) line feed AND CHAR(13) carriage return together: Some systems use CR+LF (codes 13+10) for line breaks — handle both: =TRIM(=CLEAN( SUBSTITUTE( SUBSTITUTE(A2, CHAR(13), " "), CHAR(10), " ")))

Examples 5–8: Advanced Combinations

Diagnosing, Comparing, and Converting Cleaned Data

Diagnose hidden characters — CODE, LEN, and EXACT for investigation

Before cleaning a dataset, it helps to understand which cells are affected and what characters are causing the problem. Three functions work together for diagnosis: LEN reveals whether CLEAN changes the character count, CODE identifies the first suspicious character, and EXACT confirms whether two apparently identical cells are actually different.

Detect whether a cell contains any non-printable characters. TRUE = the cell is clean. FALSE = at least one hidden character was found. LEN(=CLEAN(A2)) = LEN(A2) Identify the first hidden character in a cell. CODE returns the ASCII value of the first character. Anything below 32 is a non-printable character. CODE(LEFT(A2, 1)) → 10 = line feed, 13 = carriage return, 9 = tab, 160 = non-breaking space Confirm whether two cells that look identical are actually the same. EXACT is case-sensitive AND character-sensitive — hidden characters cause FALSE. Apply CLEAN to both before comparing for a fair comparison. EXACT(TRIM(=CLEAN(A2)), TRIM(=CLEAN(B2))) → TRUE = genuinely identical after cleaning → FALSE = content differs (not just hidden characters) Count how many cells in a column contain hidden characters: SUMPRODUCT counts rows where LEN before cleaning differs from LEN after. SUMPRODUCT(--(LEN(A2:A100)>LEN(=CLEAN(A2:A100)))) → Returns the number of cells in A2:A100 that contain hidden characters

Compare two imported lists — clean before comparing to avoid false mismatches

Two lists from different systems often appear to contain identical values but produce mismatches in VLOOKUP or COUNTIF. The reason is usually different invisible characters in each source. Cleaning both sides before comparing removes this source of false negatives entirely.

Check whether the value in A2 appears anywhere in the list B2:B100. Clean both sides before comparing — hidden characters in either list cause false mismatches. Without cleaning — may return FALSE even when values are genuinely identical: COUNTIF(B2:B100, A2) → 0 even though A2 visually matches B5 (different hidden characters) Clean the lookup value first, then search — better but still imperfect: Only cleans the lookup value; hidden chars in the list still cause mismatches. COUNTIF(B2:B100, TRIM(=CLEAN(A2))) Best approach — clean both sides using SUMPRODUCT + EXACT: Cleans every value in both ranges before comparing. Case-sensitive, space-sensitive, and hidden-character-safe. SUMPRODUCT( --(EXACT( TRIM(=CLEAN(B2:B100)), TRIM(=CLEAN(A2))))) → Returns count of exact matches after cleaning both sides

Convert text-formatted numbers back to real numbers after cleaning

Numbers imported from databases frequently arrive as text — and hidden control characters make them even harder to convert. CLEAN removes the invisible characters first. Then VALUE or a double-negative converts the cleaned string to a real number that SUM, AVERAGE, and other aggregate functions can use.

A2 contains "48500" stored as text with hidden characters. CLEAN removes the characters. VALUE converts the result to a number. =VALUE(TRIM(=CLEAN(A2))) → Returns the number 48500 (not the text "48500") Double-negative shortcut — converts text to number without VALUE(): Mathematically equivalent to VALUE() for numeric strings. --TRIM(=CLEAN(A2)) → 48500 (number) Handle currency symbols and thousands separators before converting: SUBSTITUTE removes £ and , before VALUE() can convert to a number. =VALUE(SUBSTITUTE( SUBSTITUTE( TRIM(=CLEAN(A2)), "£", ""), ",", "")) → "£48,500" with hidden chars → 48500 (number, ready for SUM)

Master cleanup formula — handle every common invisible garbage type

For heavily contaminated data — web scrapes, legacy exports, or multi-system imports — a single layered formula handles all common invisible characters in one pass. This is the formula to apply when you do not know exactly which characters are present and want the most comprehensive clean possible.

Master cleanup — handles: CHAR(160) non-breaking space, CHAR(13) carriage return, CHAR(10) line feed, all other ASCII 0–31, and extra whitespace. Build it inside-out — process each character type in the right order: 1. Remove CHAR(160) non-breaking space (SUBSTITUTE replaces with normal space) 2. Replace CHAR(13) carriage return with a space 3. Replace CHAR(10) line feed with a space 4. CLEAN removes any remaining ASCII 0–31 characters 5. TRIM normalises all spaces to single spaces =TRIM( =CLEAN( SUBSTITUTE( SUBSTITUTE( SUBSTITUTE(A2, CHAR(160), " "), CHAR(13), " "), CHAR(10), " "))) → Removes ALL common invisible garbage in one pass Add PROPER to also standardise capitalisation in a single formula: Useful for name and address fields from legacy CRM exports. =PROPER(TRIM(=CLEAN(SUBSTITUTE(A2, CHAR(160), " ")))) → Cleans AND capitalises in one formula — ideal for customer name fields

✓ Apply the master cleanup formula as the first step in every data transformation pipeline. Running it before VLOOKUP, SUMIF, or any data analysis ensures that invisible characters never cause false mismatches or missed results downstream.

Common CLEAN Issues and How to Fix Them

CLEAN did not fix the problem — spaces still remain

CLEAN only removes ASCII codes 0–31. Spaces (code 32) require TRIM. Non-breaking spaces (code 160) require SUBSTITUTE. If a cell still misbehaves after CLEAN, check the character code with =CODE(LEFT(A2,1)). A result of 32 means TRIM is needed. A result of 160 means SUBSTITUTE(A2, CHAR(160), "") is needed. A result between 0 and 31 means CLEAN should handle it — confirm by checking whether LEN(CLEAN(A2)) differs from LEN(A2).

Line break removal joins words without a space

CLEAN removes CHAR(10) line feeds but does not replace them with a space. Consequently, "John" and "Smith" separated by a line break become "JohnSmith" after CLEAN. Fix this by using SUBSTITUTE to replace the line break with a space before CLEAN runs: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(10), " "))). This inserts a space where the line break was, then TRIM collapses any double-spaces.

Numbers cleaned by CLEAN are still stored as text

CLEAN returns a text string even when the input looks like a number. The green triangle error indicator and left-aligned display confirm text storage. Wrap the formula in VALUE() to convert: =VALUE(TRIM(CLEAN(A2))). Additionally, remove any currency symbols or thousands separators with nested SUBSTITUTE calls before VALUE() attempts the conversion.

CLEAN does not handle CHAR(127) or Unicode non-printable characters: ASCII code 127 (the delete character) is not in the 0–31 range and is not removed by CLEAN. Additionally, Unicode non-printable characters above code 255 require SUBSTITUTE with the specific CHAR() code. If problems persist after CLEAN, use CODE() to identify each remaining character and add a targeted SUBSTITUTE for each one.

Frequently Asked Questions

What does the CLEAN function do in Excel?+

CLEAN removes the first 32 non-printable characters in the 7-bit ASCII table — codes 0 through 31 — from a text string. These include tab characters (code 9), line feeds (code 10), carriage returns (code 13), null characters (code 0), and other invisible control characters that database exports and web-pasted content routinely embed in cells. CLEAN does not remove spaces (code 32) or non-breaking spaces (code 160) — use TRIM and SUBSTITUTE for those.
What is the difference between CLEAN and TRIM?+

CLEAN removes non-printable control characters (ASCII codes 0–31), such as line feeds, carriage returns, and tab characters. TRIM removes extra spaces — leading spaces, trailing spaces, and multiple consecutive spaces between words. They target completely different character types. TRIM cannot remove a line feed, and CLEAN cannot remove a space. Consequently, combining both functions as =TRIM(CLEAN(A2)) is the standard starting formula for any imported text, as it covers both categories of invisible damage.
Why does TRIM not remove the invisible characters in my data?+

TRIM only removes regular spaces (ASCII code 32). If a cell has extra whitespace that TRIM does not fix, the characters are likely non-printable control characters (codes 0–31) or non-breaking spaces (code 160). Use =CODE(LEFT(A2, 1)) to identify the first character code. If the result is between 0 and 31, use CLEAN. If it is 160, use SUBSTITUTE(A2, CHAR(160), ""). For complete coverage, combine all three: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).