Extra spaces are a hidden cause of broken lookups and wrong counts. "Priya" and "Priya " look the same, but Excel treats them as different.
TRIM
=TRIM(A2)
TRIM removes spaces at the start and end, and reduces spaces between words to one.
Step by step
- Type
=TRIM(A2) in a helper column and copy it down.
- Copy the helper column.
- Right-click the original column and choose Paste Special, then Values.
- Delete the helper column.
When TRIM does not work
Data copied from websites often has non-breaking spaces (character 160), which TRIM ignores. Replace them first:
=TRIM(SUBSTITUTE(A2, CHAR(160), " "))
Remove line breaks and hidden characters
=TRIM(CLEAN(A2)) removes non-printing characters as well as extra spaces.
Check if a cell has extra spaces
=LEN(A2)<>LEN(TRIM(A2)) returns TRUE if there are extra spaces.
Worked example
Cell A2 holds " Rinkesh Shah " with extra spaces. =TRIM(A2) returns "Rinkesh Shah": it removes spaces at both ends and leaves a single space between words. Copy the result and paste as values to replace the original.
Common mistakes
- Using TRIM on text copied from the web. It does not remove non-breaking spaces. Use
=TRIM(SUBSTITUTE(A2,CHAR(160)," ")).
- Forgetting that TRIM returns a new value. The original cell is unchanged.
- Using Find and Replace for spaces, which also removes the single spaces between words.
Frequently asked questions
Why does TRIM not remove all spaces?
The cell probably has non-breaking spaces (CHAR 160), which TRIM ignores. Replace them with SUBSTITUTE first.
Does TRIM remove line breaks?
No. Use CLEAN for line breaks, or =TRIM(CLEAN(A2)).
Does TRIM fix VLOOKUP problems?
Often, yes. Hidden spaces are a common reason lookups fail.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: TRIM, CLEAN, SUBSTITUTE.
Start Excel practice → Case studies
