Imported and pasted data is full of invisible junk — extra spaces, line breaks, non-breaking spaces — that breaks lookups and totals. TRIM, CLEAN, and SUBSTITUTE scrub it in one pass.
TRIM collapses runs of spaces to one (and strips leading/trailing); CLEAN removes non-printing characters like line breaks.
The example
Names with messy spacing. The cleaned column is reliable for lookups and matching.
| A | B | |
|---|---|---|
| 1 | Raw | Cleaned |
| 2 | Maria Lopez | Maria Lopez |
| 3 | Devon Smith | Devon Smith |
The formula
The cleaned value in B2:
How it works
Each function targets a different kind of junk:
CLEAN(A2)strips non-printing control characters — the most common being the line breaks (CHAR(10)) that come from wrapped cells or web pastes.TRIMthen removes leading and trailing spaces and collapses any run of internal spaces down to a single space.- Together they turn
" Maria Lopez "into a clean"Maria Lopez"that will match and look up correctly.
The sneaky one: non-breaking spaces. Text copied from the web often contains CHAR(160), which looks like a space but TRIM won’t touch. Convert it first: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
Try it: interactive demo
Type text with extra spaces; see what TRIM(CLEAN()) returns. (Length shows the junk being removed.)
Variations
Remove ALL spaces (not just extras)
SUBSTITUTE deletes every space — handy for stripping spaces from phone numbers or codes:
Kill non-breaking spaces from web data
The full belt-and-suspenders clean:
Remove a specific character
Strip out, say, every comma:
Pitfalls & errors
Lookups still fail after TRIM. The culprit is almost always CHAR(160), the non-breaking space, which TRIM ignores. SUBSTITUTE it to a normal space first.
CLEAN misses some modern characters. CLEAN only removes the first 32 control characters. Stubborn invisibles (zero-width spaces, CHAR(160)) need SUBSTITUTE.
Convert formulas to values before deleting the original. Copy the cleaned column, Paste Special → Values, then you can remove the raw column safely.
Practice workbook
Frequently asked questions
How do I remove extra spaces in Excel?
Why does TRIM not remove all spaces?
How do I remove all spaces, not just extra ones?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class