The TRIM function strips leading and trailing spaces and collapses repeated spaces between words down to one. It does not remove non-breaking spaces pasted from the web (character 160). For those, nest CLEAN or SUBSTITUTE.
Imported CSVs, copied emails, and VLOOKUP that "should match" but do not are the usual reason you need it.
Syntax
=TRIM(text)
- text — a cell or a string. TRIM returns the cleaned text.
Worked example
| A | B (formula) | Result |
| Lee | =TRIM(A2) | Lee |
| Patel | =TRIM(A3) | Patel |
| Ann Marie | =TRIM(A4) | Ann Marie |
Clean a column, then look things up
VLOOKUP and COUNTIF treat "Lee" and "Lee " as different values. Clean the key first:
=VLOOKUP(TRIM(E2), A2:C100, 3, FALSE)
or write a helper column =TRIM(A2) and look up against that.
If Remove Duplicates reports 0 matches on a list that looks repeated, run TRIM first. That is the most common reason the remove-duplicates tool sees nothing.
Non-breaking spaces (the TRIM that "does nothing")
Web tables often use character 160 instead of a normal space. TRIM ignores those. Replace them, then trim:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))
Common errors
- Looks the same after TRIM — the extra character is not a space. Use =CODE(MID(A2,n,1)) to inspect character by character; 160 is the usual culprit.
- Numbers stored as text with spaces — TRIM the cell, then multiply by 1 (=TRIM(A2)*1) to coerce a real number.
- #VALUE! — you passed an error, not text. Fix the source cell first.
Frequently Asked Questions
What does TRIM remove?
Leading spaces, trailing spaces, and extra spaces between words. It leaves a single space between words. It does not remove tabs, line breaks, or non-breaking spaces on its own.
Does TRIM change the original cell?
No. It returns a new value. Write it in a helper column, then copy → paste values over the source if you want the cleanup to stick.
TRIM vs CLEAN?
CLEAN removes non-printable characters (codes 0–31). TRIM removes extra spaces. Use both on dirty imports: =TRIM(CLEAN(A2)).
Why does TRIM not fix my VLOOKUP?
The lookup value and the table key both need to be clean. TRIM only the lookup cell if the table still has trailing spaces, and the match still fails.