Text
How to Use TRIM in Excel (Remove Extra Spaces Fast)
TRIM removes extra spaces from text, leaving only single spaces between words and none at the start or end. It is the first thing to try when data imported from another system has invisible spaces that break lookups and comparisons.
Those stray spaces are a leading cause of VLOOKUP and matching failures, because "Paid " and "Paid" look identical but are not equal. This guide covers TRIM, when to pair it with CLEAN for non-printing characters, and how to make the cleanup stick.
TRIM syntax
=TRIM(text) | Argument | Required | What it does |
|---|---|---|
text | Required | The cell or text you want to clean of extra spaces. |
Note: TRIM only removes the standard ASCII space (character 32). It will not remove non-breaking spaces (CHAR(160)) from web data; pair it with CLEAN for non-printing characters or SUBSTITUTE for non-breaking spaces. Works in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Remove extra spaces from text
=TRIM(A2) Collapses runs of spaces to a single space and strips leading and trailing spaces, turning " John Smith " into "John Smith".
2. Fix a VLOOKUP that fails on spaces
=VLOOKUP(TRIM(A2), E:F, 2, FALSE) Wrapping the lookup value in TRIM removes stray spaces so the match succeeds even when the source data has hidden trailing spaces.
3. Remove line breaks and non-printing characters
=TRIM(CLEAN(A2)) CLEAN removes non-printing characters like line breaks; TRIM then tidies the leftover spaces. A common one-two punch for imported data.
4. Remove non-breaking spaces from web data
=TRIM(SUBSTITUTE(A2, CHAR(160), " ")) Web data often contains CHAR(160), a non-breaking space TRIM ignores. SUBSTITUTE swaps it for a normal space first, then TRIM cleans up.
How to write TRIM step by step
- 1
Click an empty helper cell and type =TRIM(
- 2
Click the cell with the messy text, then close the bracket ) and press Enter.
- 3
If line breaks or odd symbols remain, wrap it as =TRIM(CLEAN(A2)).
- 4
For non-breaking spaces from the web, use =TRIM(SUBSTITUTE(A2, CHAR(160), " ")).
- 5
Copy the helper column, then Paste Special and choose Values over the original to lock in the cleaned text.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Spaces remain after TRIM | The text contains non-breaking spaces (CHAR(160)), which TRIM does not remove. | Replace them first: =TRIM(SUBSTITUTE(A2, CHAR(160), " ")). |
Odd boxes or characters remain | The text has non-printing control characters such as line breaks. | Add CLEAN: =TRIM(CLEAN(A2)) removes most non-printing characters. |
Numbers turn into text | TRIM always returns text, even for numeric-looking input. | Convert back with VALUE or by multiplying by 1, e.g. =VALUE(TRIM(A2)). |
Lookups still fail | One side has spaces and the other does not, or the data types differ. | Apply TRIM to both the lookup value and the table, and confirm both are the same data type. |
Frequently asked questions
What does TRIM do in Excel?
It removes leading and trailing spaces and collapses multiple spaces between words into one, leaving the text neatly spaced. It is the standard fix for stray spaces in imported data.
Why does TRIM not remove all spaces?
TRIM only removes the standard space character (ASCII 32). Data copied from the web often contains non-breaking spaces (CHAR(160)), which you must replace with SUBSTITUTE first.
What is the difference between TRIM and CLEAN?
TRIM removes extra spaces; CLEAN removes non-printing characters such as line breaks and control codes. They are often combined as =TRIM(CLEAN(A2)) to fully tidy imported text.
How do I make the cleaned text permanent?
Use TRIM in a helper column, copy it, then Paste Special and choose Values over the original cells. This replaces the messy text with the cleaned result.