TRIM in Excel: remove extra spaces that break lookups

A space at the end of “Ana ” makes it a different value from “Ana”. Lookups fail, duplicates survive, filters show the same name twice. TRIM removes those spaces.

What TRIM removes

• Spaces before the text
• Spaces after the text
• Extra spaces between words (two or more become one)

Steps

1. Messy text in column A.
2. Click B1. Type =TRIM(A1). Enter. Drag down.
3. Select column B, copy, then right-click column A › Paste Special › Values.
4. Delete column B.

Column A is now clean, and it is plain text, not formulas.

Check it worked

=LEN(A1) before and after. “Ana ” has 4 characters; “Ana” has 3.

The space TRIM cannot remove

Text copied from web pages often contains a non-breaking space (character 160). TRIM ignores it. Use both:

=TRIM(SUBSTITUTE(A1, CHAR(160), " "))

Clean and look up in one go

=XLOOKUP(TRIM(D1), A:A, B:B)

Faster for a one-off

Ctrl + H, Find what: two spaces, Replace with: one space, Replace All — repeat until it finds nothing. This handles middle spaces but not the ends; TRIM handles all three.