Want to learn how to remove those pesky stubborn spaces that even the TRIM function can't get rid of?
Here is an easy fix that will have you up and running and getting rid of your spaces before you know it.
When TRIM function doesn't work on numbers
There's no doubt that the TRIM function is fabulous, but sometimes it just doesn't work for you.
First of all, let's look at a number with a space.
The easiest way is doing a Find and Replace. So you can actually find the space and replace it with nothing.
Using Ctrl + H will open up the Find and Replace box.
Inside the Find what text box press the spacebar on the keyboard and replace it with nothing by making sure there is no text in the Replace with text box.
Click Replace and it'll fix it for you. Click OK and there you go.
When TRIM function doesn't work within text
That's one option, now let's talk about the spaces found within text. For example in rows 7 to 10 in the example below.
Now the reason the TRIM function hasn't removed these is because they aren't normal spaces.
These spaces are non-breaking spaces, sometimes referred to as hard spaces, and TRIM only removes normal spaces.
These non-breaking spaces can be replaced. For example there are quite a few spaces in front of Sarah Brooke’s name as well as one in between.
What you can do is you can highlight over one of these spaces and you can then copy it using Ctrl + C.
Then highlight over the cells where these spaces actually are in your worksheet.
Now, use Replace, Ctrl +H.
Use Ctrl + V to paste the hard space that you’ve just copied into the Find what text box. Replace this with a normal space by pressing the spacebar on the keyboard in the text box next to Replace with.
Now do a Replace All and click OK.
Now TRIM is working because all of the non-breaking spaces have been replaced by normal spaces and TRIM is able to clean these up.
Was this Blog helpful? Let us know in the comments below.