TRIM
Removes extra spaces: at the start and end of text and doubled ones between words. Useful when data comes from an export or was typed by hand.
TRIM(text)- text
- The cell with the text to clean up, for example A2.
Return value
The same text without spaces at the start and end, with the spaces between words squeezed down to one.
Examples
Cleaning names from an export
fx
A
B
C
D
E
1
2
3
4
5
6
7
From export
Cleaned
Characters before
After
John Smith
Emily Davis
Mike Brown
Sarah Johnson
Sheet1
Details
TRIM returns the same text without spaces at the edges and turns several spaces in a row between words into one.
You can't see an extra space, but for Excel "Boston " and "Boston" are different values. Because of this, counts don't add up and VLOOKUP can't find the key.
Write the formula in the next column. To replace the original data, copy the result and paste it as values.
Common mistakes
- TRIM keeps a single space between words, so it won't remove the space inside a code like "AB 1234". Use SUBSTITUTE for that.
- Spaces in data copied from websites are sometimes a special non-breaking space. It's a different character, and TRIM doesn't touch it.
- The source column was deleted while the next column still has formulas: they break with #REF!. Paste the result as values first.