The Excel TRIM function strips the spaces nobody wants: every leading space, every trailing space, and every run of spaces between words is collapsed to a single space. It is the first thing to reach for when a lookup mysteriously can’t find a value that is plainly right there — an invisible trailing space is the usual culprit. One important catch: TRIM does not remove the non-breaking space that web pages paste in, and this page shows the fix.
" Maria Lopez " becomes "Maria Lopez" — ends stripped, the inside run collapsed to one space. For text pasted from the web, use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Syntax
| Argument | Description | |
|---|---|---|
text | Required | The text (or cell reference) to clean. Leading and trailing spaces are removed; internal runs of spaces are collapsed to a single space. |
What counts as a space? Only the regular space character — ASCII 32. The non-breaking space (CHAR(160)) that HTML pages use is a different character and sails straight through TRIM. Tabs and line breaks aren’t spaces either; those are CLEAN’s department.
Strip the extra spaces
Names exported from an old system arrive padded and double-spaced. One TRIM per cell normalizes all of it — and the LEN column proves characters really disappeared:
| A | B | C | |
|---|---|---|---|
| 1 | Pasted name | =TRIM(A2) | =LEN(A2) → LEN(B2) |
| 2 | Maria Lopez | Maria Lopez | 14 → 11 |
| 3 | James Chen | James Chen | 11 → 10 |
| 4 | Priya Patel | Priya Patel | 15 → 11 |
| 5 | Dan Brooks | Dan Brooks | 13 → 10 |
Note what TRIM keeps: exactly one space between words. To remove every space, that’s SUBSTITUTE:
Try it: interactive TRIM demo
Paste or type messy text — spaces show as · and non-breaking spaces as ° so you can see exactly what TRIM removes (and what it misses).
Fix broken lookups — and the CHAR(160) web-paste trap
The lookup repair. When =XLOOKUP("ACME", A:A, B:B) returns #N/A but you can see ACME in the column, a trailing space is almost always hiding in the data. Trim the lookup array on the fly:
Or fix the data itself with a helper column of =TRIM(A2), then paste-special as values over the original.
The CHAR(160) trap. Text copied from web pages often contains non-breaking spaces — character 160, not 32. They look identical, but TRIM ignores them. The bulletproof cleaner substitutes them into regular spaces first, then trims:
Add CLEAN for imports that also carry line breaks and control characters — the full scrubber:
How do you know spaces are the problem? Compare lengths — if these differ, invisible characters exist:
Errors & common pitfalls
Pitfall: TRIM doesn’t touch CHAR(160). The single most common “TRIM isn’t working” report. Non-breaking spaces from web pastes look like spaces but aren’t ASCII 32. Fix: =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Pitfall: internal spaces are collapsed too. TRIM is not “trim the ends” like in most programming languages — it also squeezes every internal run down to one space. If your data legitimately contains double spaces, TRIM will eat them.
Pitfall: numbers come back as text. =TRIM(A2) on the number 42 returns the text “42”, which SUM ignores. Convert back with --TRIM(A2) or VALUE(TRIM(A2)) when trimming numeric columns.
Pitfall: tabs and line breaks survive. TRIM only knows the space character. Tabs (CHAR(9)) and line breaks (CHAR(10)) from imports need CLEAN: =TRIM(CLEAN(A2)).
Pitfall: TRIM doesn’t edit the cell. Like all formulas it returns a cleaned copy. To repair the data in place, put TRIM in a helper column, copy it, and Paste Special › Values over the original column.
Practice workbook
Frequently asked questions
Why is TRIM not removing spaces from my web-pasted data?
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")).Does TRIM remove spaces between words?
=SUBSTITUTE(A2," ","").What's the difference between TRIM and CLEAN?
=TRIM(CLEAN(A2)).Why does my VLOOKUP fail even though the value is clearly there?
=LEN(A2)-LEN(TRIM(A2)) - anything above 0 means excess spaces. Then trim the lookup array in the formula or clean the column with a TRIM helper column.How do I TRIM a whole column at once?
=TRIM(A2:A100). In older versions, enter =TRIM(A2) in a helper column and fill down. Either way, finish with Copy then Paste Special › Values if you want to replace the original data.Master functions like this in one day
This page covers one function. Our Excel Formulas and Functions class covers the 30 that matter most — live, hands-on, taught by professionals in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class