April 19

0 comments

Remove spaces Excel TRIM function misses

By Sharyn Baines

April 19, 2021

Functions, Spaces, spacing, TRIM

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.

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. 

Excel remove spaces

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.

Excel remove spaces

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.

Tip: Extra leading or trailing spaces can be identified by clicking your insertion point into the Formula bar. If the insertion point isn’t flush up against the beginning or end of your text, you have extra spaces.

Excel remove spaces
Excel remove spaces

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. 

Excel remove 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.

Excel remove spaces

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. Replace this with a normal space by pressing the Spacebar on the keyboard in the Replace with text box.

Excel remove spaces

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

Excel remove spaces

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.

If you enjoyed this post check out the related posts below.

Sharyn Baines

About the author

Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand. She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

JOIN FREE! 'Excel at Work Insiders Group' for free tutorials, mini-courses, videos and blogs!

>