How To Remove Spaces in Excel (When TRIM Doesn’t Work!)
Вставка
- Опубліковано 29 жов 2024
- 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:
tinyurl.com/4v...
📊Download File Link
tinyurl.com/37...
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 UA-cam 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:
tinyurl.com/4v...
📊FIVE-MINUTE MONDAYS PLAYLIST
tinyurl.com/394axzjp
Of all internet, you were the first to provide a good and useful answer. Thanks, and congratulations
Thank you very much, Pablo! Happy this video helped you. Hope to see you in another one on the channel ...
You are a life saver! Thank you so much for this guide!
It's a pleasure - glad it helped and see you in another video on the channel
This is pretty common copying from PDFs in my experience. Thanks for the solution!
That's another situation to look out for! Thanks for watching ...
Trimming is everywhere this morning trying to trim beard scraped skin instead. Nice video Chris!
Got to look sharp for those Excel sessions, Nitin! I hope the video helps ...
It is helping
Great to hear 🙌
Dear Sir.. This is a work of Art! Thankyou
you're very generous - I'm glad it helped!
looking for this kind of formula. thanks for sharing
It's a pleasure Krishna - I hope it helps!
@@TigerSpreadsheetSolutions very much. from few months I tired to remove extra spaces manually
Yes, it should be much faster with a formula. Then Power Query and VBA can also help.
Thank u Chris for this insightful Video 👍
You’re welcome, Nader!
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.
finally! you're the man!
thank you and welcome to the channel!
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?
The CLEAN formula might help you - check that out
I just checked a video of CLEAN formula and it's indeed very cool, but they did not show an example where the unprintable characters are inbetween the words....@@TigerSpreadsheetSolutions
Please email me your file - contact address on the website
Really helped.
Great to hear and thanks for watching. Do check out some of the othe videos on the channel ...
Best solution
Thank you and see you in another video!
=Trim(Substitute(A1, Char(160),""))
Thank you very much for the clarification! I've hated it for years!
you're welcome - thank you for watching!
To be honest it'll be easier and quicker to just click the cell and delete the space !!
and if you have 2000 rows ...?