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
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.
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.
To remove these spaces from the Excel cell you would need to
- highlight the gap in one of the cells
- 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)
- 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.