How To Remove Spaces in Excel (When TRIM Doesn’t Work!)

You’ve probably heard me talk on the channel about the importance of debugging in Excel. It’s just so … important! Since, if you can ‘stay in the game’ when things go wrong and work things out steadily and systematically (without throwing your laptop out of the window!) there’s actually no limit to how far you can go with Excel …

🔥Members' Monday FREE 1-hour taster session:

📊Download File Link

It’s a ‘metaskill’ - a key competence that transcends everything you’re doing in your analytical work. I take some pride in my ability to work things out, no matter how complex or counter-intuitive Excel’s behaviour seems. And we’ve all been there!

This one, however, completely stumped me.

It concerns a perennial problem in Excel: how to identify and remove unwanted spaces from cell entries. When a cell appearing to contain five characters actually contains six, because a space has crept in behind the text. How to deal with it?

‘But that’s easy Chris!’, I hear you say, ‘Don’t you know about the =TRIM formula?!’

I find =TRIM handles a single unwanted space well. But, it struggles in two situations: first, if there’s an additional unwanted space (yes, it happens!) after the first, =TRIM seems to retain it – reducing two spaces to one, which doesn’t solve the problem. You could alleviate this by ‘trimming the =TRIMS’ and applying the formula twice – as I’ve done in the Excel download file for today’s video.

But the second situation is considerably worse. As I explain in the video, I found =TRIM simply wouldn’t do its job in a specific scenario – and it drove me to distraction! The reason for this was incredibly difficult to deduce. It turns out not all spaces are created equal. In fact, not all spaces are actually treated as ‘spaces’ by Excel …

Let me explain. There’s another ‘space’, which you can access using the =CHAR(160) formula, which looks exactly the same as a normal space, but behaves differently. Yes, =TRIM doesn’t identify =CHAR(160) as a space, though it looks exactly the same! I’ve found =CHAR(160) crops up when copying text into Excel from another programme or data source. In my project, for example, we were importing text from an online form. It’s a common thing to do, which is why you must know about =CHAR(160)!

In the video, I walk you through the problem using the example of four pieces of text (‘Tiger’, ’Tiger’, ‘Tiger’ and ‘Tiger’!) These four entries look the same but actually consist of different characters – something that’s impossible to spot without the application of Excel formulae. I apply Excel formulae including =TRIM, =LEN, =FIND and =SUBSTITUTE to cleanse the data and leave us with a single text entry ‘Tiger’ – consisting of five characters, no more and no fewer.

I hope the video saves you some time when dealing with unwanted spaces in Excel! Let me know how you get on in the YouTube comments.

TIMESTAMPS
00:17 Excel TRIM Formula Demo
00:33 How To Remove A Space After A Cell Entry
01:03 What’s Char(160) In Excel?
02:00 Excel FIND Formula Demo
02:29 Excel CHAR Formula Demo
03:01 How Char(160) Can Occur In Excel
03:33 Combining FIND With Char(160)
04:01 Excel SUBSITUTE Formula Demo
05:14 About Our Members’ Monday Community
-------------------------------------------
🔥ABOUT MEMBERS' MONDAY
This video is taken from our unique and exclusive Members' Monday learning community.

Looking to build your data analysis skills in a supported environment with expert access, with a group of like-minded individuals?

Need structured materials with a practical focus, and a place to go for help?

Want to take a long-term approach to your learning and get real improvement, rather than lurching from one problem to the next?

You'll love our Members' Monday community!

"I had a massive breakthrough this morning solving a problem with a file I have been unhappy with for seven years!" - RECENT MEMBER FEEDBACK

🔥Members' Monday FREE 1-hour taster session:

[ChristopherMc-ki]

To be honest it'll be easier and quicker to just click the cell and delete the space !!

[omrin]

Okay but what if I have two words and an ascii in between that's not char(160)? How am I supposed to find and substitute it?

[pablofleiss]

Of all internet, you were the first to provide a good and useful answer. Thanks, and congratulations

[albasoley]

That is amazing! Thank you, but how did you find which is the Char160? It doesn't seem to work for me... I would like to know which is exactly the charcter that is generating the space for me.

[cammac]

This is pretty common copying from PDFs in my experience. Thanks for the solution!

[thuyduongnguyen]

=Trim(Substitute(A1, Char(160), ""))

[nitinmishra]

Trimming is everywhere this morning trying to trim beard scraped skin instead. Nice video Chris!

[adamhargrave]

You are a life saver! Thank you so much for this guide!

[krishnamanjunatha]

looking for this kind of formula. thanks for sharing

[nadermounir]

Thank u Chris for this insightful Video 👍

[robertcabello]

Dear Sir.. This is a work of Art! Thankyou