April 19

0 comments

When the TRIM function doesn’t work

By Sharyn Baines

April 19, 2021

Functions, Spaces, spacing, TRIM

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. 

remove spaces the trim function does not catch

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.

why trim function doesn't work

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.

trim function does not work in excel

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.

trim function does not work

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.

trim function won't work

Now do a Replace All and click OK.

trim function not working

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.

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!

>