TRIM Function in Excel: Remove Extra Spaces From Data
Use TRIM in Excel to remove leading, trailing and double spaces that break lookups and counts, plus the non-breaking space fix for web data.
By Ayoub Farid · · 1 min read
Extra spaces are invisible and they break everything: VLOOKUP returns #N/A, COUNTIF misses rows, duplicates don't look like duplicates. TRIM fixes most of them.
What TRIM does
=TRIM(text)- Removes spaces at the start and end
- Turns runs of spaces in the middle into one space
" Sam Lee " becomes "Sam Lee".
Clean a whole column
- In an empty column next to the data, write
=TRIM(A2)and fill down. - Copy the new column.
- Paste Special > Values over the original (or keep the clean column and delete the old one).
In Excel 365 or Google Sheets you can trim a whole range at once: =TRIM(A2:A500) spills the results.
TRIM inside other formulas
Instead of cleaning the data, trim on the fly:
=VLOOKUP(TRIM(F2),$A$2:$C$500,3,FALSE)
=PROPER(TRIM(A2))When TRIM doesn't work: non-breaking spaces
Data copied from web pages often contains non-breaking spaces (character 160). TRIM ignores them. Replace them first:
=TRIM(SUBSTITUTE(A2,CHAR(160)," "))Other invisible characters
Line breaks and control characters from exports: use CLEAN.
=TRIM(CLEAN(A2))How to spot hidden spaces
=LEN(A2) counts characters. If "Latte" gives 6, there's a space. Compare =LEN(A2) with =LEN(TRIM(A2)).
Related clean-up functions
- UPPER, LOWER, PROPER for case (covered with TEXT)
- LEFT, RIGHT, MID to cut text
- Split text into columns
- Remove duplicates
Cleaning messy data (TRIM, TEXT, dates) is one of the units coming to Cellio.
Frequently asked questions
How do I remove spaces in Excel?
Use =TRIM(A2) to remove extra spaces, or =SUBSTITUTE(A2," ","") to remove all spaces.
Why doesn't TRIM remove all spaces?
The data contains non-breaking spaces. Use =TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
Does TRIM remove spaces between words?
It keeps single spaces between words and removes extra ones.
Does TRIM work in Google Sheets?
Yes.