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 Here

CLICK HERE TO WATCH THE VIDEO

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