Remove spaces Excel TRIM function misses

Want to learn how to remove spaces in Excel that even the TRIM function can't get rid of?

Here are two easy fixes that will have you up and running and getting rid of your spaces before you know it.

[Watch on YouTube]  /  [Subscribe to our YouTube Channel]

Fix #1 Excel remove spaces in numbers

There's no doubt that the TRIM function in Excel is fabulous, but sometimes it just doesn't work for you.

First of all, let's look at a number with a space.

In the example below A4 contains a number with a space. In B4 the TRIM function has been used to remove the space. However, the space remains.

The easiest way to remove spaces in numbers is to do a Find and Replace. Simply find the space and replace it with nothing.

Step 1: Press Ctrl + H to open the Find and Replace dialog box. 

Step 2: Inside the Find what text box press the spacebar on the keyboard. We are now going to replace the space with nothing, so make sure there is no text or spaces in the Replace with text box.

Step 3: Click Replace or Replace All and the space will be replaced with no space. Click Close and there you go.

Fix #2 Remove leading and trailing spaces in Excel

Now let's talk about extra leading and trailing spaces found within cells containing text.

Sometimes the TRIM function in Excel seems to not be working on cells containing text. For example, in rows A7 to A10 in the example below you can see that each cell holds extra spaces.

Cells A7 and A10 contain extra leading spaces.

Cell A8 contains extra spaces between Robert’s first and last name.

Cell A9 has extra trailing spaces. Trailing spaces aren’t easily identified until you select the cell and then click into the Formula bar (more on this later). 

The TRIM function is being used in cells B7 to B10 in the hope that it will remove the extra spaces. However, the spaces have remained.

In the example below, there are quite a few leading spaces in front of Sarah Brooke’s name, as well as one trailing space at the end of her name.

The reason the TRIM function hasn't removed these extra spaces 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.

Thankfully, these non-breaking spaces can easily be replaced. 

To do this we will use Find and Replace once more, but this time it includes a new step.

Step 1: click into the Formula bar and highlight over one of the spaces. 

Step 2: Copy the space (press Ctrl + C).

Step 3: Now select over the cells in your worksheet that contain the stubborn extra spaces. In our example we have selected cells A7 to A10.

Step 4: Now, we will use Find and Replace. Press Ctrl +H to open the Find and Replace dialog box. 

Step 5: Use Ctrl + V to paste the hard space that you’ve just copied into the Find what text box. Tip: if you haven't managed to copy the space you can also use the keyboard combination Alt+0160 to pop a hard-space into the Find what box. Replace this with a normal space by pressing the Spacebar on the keyboard in the Replace with text box.

Step 6: now click Replace All and then click Close.

Now the TRIM function will work because all of the non-breaking spaces have been replaced by normal spaces and TRIM is able to remove the extra spaces.

Was this Blog helpful? Let us know in the Comments below.