Remove spaces in Excel when trim doesn’t work

An increasingly regular problem is to remove spaces in Excel when TRIM doesn’t work. TRIM is a great function for removing any extra spaces before and after a set of characters and limiting any spaces between words to a single space. But sometimes this still doesn’t work.

YouTube Remove spaces that Excel does not recognize

Remove all the spaces including the spaces between words

If you don’t want to see even the single spaces you need to replace all the spaces with nothing. If you were using TRIM in a formula then the correct formula to rather use is SUBSTITUTE.

So below in the SUBSTITUTE formula we tell it to

  • look at cell A2,
  • find all the ” ” (notice the gap between the inverted commas generated by clicking the space key on the keyboard)
  • replace them with “” (notice no gap- we are telling it to replace with nothing)
  • The instance number is left blank as we want everything to be replaced.
Remove spaces in Excel when trim doesn't work

What if Excel says there are no spaces (but you can see them!)

If you do the above, or do a Find Replace, and Excel seems to ignore it and leave the spaces in, then it is likely that the ‘space’ you are looking at is not a space generated by your keyboard. There is an explanation for all of this but the quick fix is to copy the ‘space’ and paste that into your substitute function.

So below we try the same thing and it doesn’t seem to make a difference.

Remove spaces in Excel when trim doesn't work

To remove these spaces from the Excel cell you would need to

  • highlight the gap in one of the cells
Remove spaces in Excel when trim doesn't work
  • Copy the gap (easiest with CTRL + C)
  • Create the SUBSTITUTE formula but paste (CTRL + V) this item into the Old Text option (you won’t really see the difference but Excel sees the different type of space)
Remove spaces in Excel when trim doesn't work
  •  Now Excel will replace these so called spaces with nothing.

Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course  or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.

Removing spaces from cells, even when it isn’t a space

Remove characters in excel cells

Excel not recognizing spaces