Excel TRIM Function: Remove Extra Spaces From Text
Strips leading, trailing and repeated spaces, leaving single spaces between words.
TRIM removes spaces from the start and end of a text value, and collapses runs of spaces inside it down to one. It exists because imported data is full of padding you cannot see, and because that padding silently breaks everything downstream — lookups miss, duplicate checks pass, groupings split in two.
It is precise about what it removes. Single spaces between words survive; everything else goes. " bruno santos " becomes "bruno santos", with the internal double space collapsed as well as the padding stripped.
There is one important thing it does not do, and it catches everyone. Text copied from a web page or a PDF often contains non-breaking spaces — character 160, not the ordinary character 32 — and TRIM leaves those exactly where they are. If TRIM appears to do nothing, that is almost always why.
Syntax
=TRIM(text)Arguments
textRequired- The value to clean. Numbers pass through unchanged, since they have no spaces to remove.
The example data
Headers in row 1, data in A2:C5. Note the stray spaces in A3 and A5.
| A | B | C | |
|---|---|---|---|
| 1 | Name | Code | |
| 2 | Alice Moreau | alice@northwind.com | NW-2024-001 |
| 3 | bruno santos | bruno@southgate.co.uk | SG-2024-014 |
| 4 | CHEN WEI | chen@northwind.com | NW-2023-207 |
| 5 | Dana Okafor | dana@eastvale.org | EV-2024-092 |
Worked examples
=TRIM(A3)Result: bruno santos
Leading and trailing spaces removed from the padded name.
=LEN(A3)-LEN(TRIM(A3))Result: 4
How much padding there was. A quick audit column for spotting which rows are dirty.
=PROPER(TRIM(A3))Result: Bruno Santos
TRIM is usually a step rather than a destination — clean, then fix the capitalisation.
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))Result: Alice Moreau
The version that also handles non-breaking spaces. Use this one on anything pasted from a browser.
Now practise it
Reading about a formula is not the same as writing one. Open this function's exercise and type it into a real grid — you get instant feedback on exactly which cell is wrong and why.
Common errors and how to fix them
TRIM appears to do nothing
Why it happens: The spaces are non-breaking (character 160), which TRIM does not touch. Web and PDF copies are full of them.
How to fix it: Convert them first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
Numbers turn into text
Why it happens: TRIM always returns text, so TRIM applied to a number gives you a number stored as text.
How to fix it: Wrap it in VALUE when the column needs to stay numeric: =VALUE(TRIM(A2)).
Line breaks survive
Why it happens: TRIM removes spaces, not other whitespace. A line break inside a cell is character 10 and stays put.
How to fix it: Remove them with CLEAN, or substitute directly: =TRIM(SUBSTITUTE(A2, CHAR(10), " ")).
Tips worth knowing
- When a lookup fails on a value you can see, TRIM both sides before assuming the data is missing.
- TRIM the source once with a helper column and paste-special as values, rather than wrapping every downstream formula in it.
- CLEAN removes non-printing characters; TRIM removes spaces. Messy imports usually need both.
- For the full treatment on pasted web data: =TRIM(CLEAN(SUBSTITUTE(A2, CHAR(160), " "))).
Frequently asked questions
Why is my VLOOKUP still failing after TRIM?
Either the space is non-breaking — character 160, which TRIM ignores — or the mismatch is not a space at all but a number stored as text on one side. Compare with =A2=B2 and check =LEN() on both cells to see which.
Does TRIM remove spaces between words?
It collapses multiple spaces between words down to a single one, but never removes that last space. "bruno santos" becomes "bruno santos", not "brunosantos".
How do I remove all spaces, including between words?
Use SUBSTITUTE rather than TRIM: =SUBSTITUTE(A2, " ", "") strips every space. This is what you want for reference codes that should have none, and not what you want for names.
Related functions
Guides that use it
Longer reads where this function does real work in a real sheet.