Title: Remove spaces in Excel when trim doesn't work • AuditExcel.co.za
Headings: Remove spaces in Excel when trim doesn’t work
Remove spaces in Excel when trim doesn’t work
Remove all the spaces including the spaces between words
What if Excel says there are no spaces (but you can see them!)
Subscribe to our YouTube Channel
Subscribe Now
Finding errors in your Excel spreadsheets with ExcelAnalyzer
Find inconsistent and overwritten formula in Excel. These are the easy spreadsheet errors to find.

Content: Remove spaces in Excel when trim doesn't work • AuditExcel.co.za





Home // Blog & Excel Tips // Remove spaces in Excel when trim doesn’t work
Remove spaces in Excel when trim doesn’t work
Adrian
Comments Closed
May 15, 2017
Oct 25, 2018
Uncategorised
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.