Remove ALL Unwanted Spaces in Excel (TRIM ALL Function?)
ฝัง
- เผยแพร่เมื่อ 14 พ.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
Stubborn spaces can become a headache in Excel. You'll come across extra spaces when you copy data from your email or import data from another system. Each cell value ends up having trailing or leading spaces. Sometimes they can be removed easily with the TRIM function but sometimes TRIM doesn't work! In this video I'll show you how you can TRIM ALL Spaces - also the stubborn ones.
⬇️ DOWNLOAD the workbook here: pages.xelplus.com/trim-spaces...
TRIM can only remove spaces with an ASCII character code 32. But there is another character code that represents a space. It's commonly referred to as a Non-Breaking Space and its ASCII character code is 160. Such spaces commonly occur when we copy text from Outlook or a website to Excel. With the Excel function CODE() we can identify which character code we are dealing with.
To remove a non-breaking space in Excel we can use the SUBSTITUTE function. With this function we can look for character code 160 in a cell and replace it with a regular space.
For numbers that are not recognized as numbers by Excel we can use the VALUE function. If the number contains non-breaking spaces use it in combination with the SUBSTITUTE function.
🔎 Video Insights:
- Understand why the TRIM function might fail to remove certain types of spaces.
- Learn about the non-breaking space (ASCII character code 160) often encountered in data from external sources.
- Master the SUBSTITUTE function to eliminate pesky non-breaking spaces.
- Discover how to use the VALUE function effectively when Excel fails to recognize numbers.
💡 Key Takeaways:
- Tackle common but tricky Excel challenges with confidence.
- Learn how to clean and format your data correctly, saving time and reducing errors.
- Enhance the reliability of your data analysis with these expert tips.
00:00 What To Do When VLOOKUP Does NOT Work
01:01 What to Do When TRIM Does Not Remove Spaces
03:36 Use SUBSTITUTE to Remove Spaces Between Words
04:49 What To Do When Excel Does Not Recognize Numbers
07:20 Wrap Up
Check out other Excel tips and tricks in this playlist: • Excel Hacks: Tips & Tr...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/trim-spaces-file
Another OMG from me! I thought I knew the trim function and nearly skipped this video. Not only i learnt so much more but also learnt not to skip your videos!!!
😁 Thank you!
Hi, I'm from Brazil and watch everything I can from the channel. Thank you very much for the tips, your insights are very interesting and make the daily routine of those who use Excel as a work tool a lot easier. Good job, I wish you much success!
Straight to the point. I've been searching this method for an hour. Thanks for the tricks!
You are awesome. Have done a few of your courses. The clarity in your explanations is of highest order.
Leila, you are truly my HERO, I SO APPRECIATE YOU! This lesson has just allowed me to save 4hours of formatting. Thank you, thank you, thank you.
The best video. Solution to my problem when I convert PDF to excel. Thanks for sharing your knowledge.
Once again i'm speechless. The quality of explanation, including a workbook to test everything, is outstanding.
Thank you! I'm glad you like the content.
I know about Trim(). But the explanation you have given, has never seen before. thankyou.
Dear Leila, thank you so much for your sharing your knowledge with us. Your videos have been helping me a lot with my new job. Please keep up the good work!
Hey Leila, Thank you so much for this video it helped me alot to clean my data. Keep posting these kind of videos. Its a great belp for many people who are working on Excel. Thank you so much ❤️❣️
this was a super help for me i used this today & fixed a lot of issues with spaces on my spreadsheet. thanks so much for your time and detication for these tutorials they are a really help for a lot of people worldwide !!!! awesome work !!!
Wow! Just Wow! Thank you for being such a marvellous human being Leila. After struggling all week to figure this out I could almost cry after listening to your wonderful voice explaining the way forward in such perfectly simple terms. Thank you!
You're so welcome, Bill!
This was exactly what I was looking for to remove the blank spaces. Using the Code function also was helpful in identifying the ASCII code for the spaces in my sheet.
You are the best, smoothly, simple , choose the simplest way which we can understand easily, thanks a lot for your excellent work
Learning never ends. Leila, your videos inspire to learn more and more...Keep this up.
Hi Leila, I just bought your course on Udemy. You are a mentor for me! I will study hard and practice!
Thanks Leila, you have always understand the problems before I encounter them.
Leila, you are a star. I have been struggling to find a youtube video that can fix this issue. You are an absolute star 🌟
I've had no idea about the non-breaking space until today. Thank you, Leila! This will save me a tonne of time.
My pleasure. Glad you like it.
This is an incredible tutorial video. It is so refreshing seeing women, attractive at that, competently talk about technical stuff
Hi 👋 Leila Excellent coaching technique I watch your videos because I learn something useful and innovative Thanks a lot for helping us Keep the good work going 👍
I wish you'd made this video last week because I encountered exactly this problem on Sunday whilst reconciling expenses. Based on topic expertise, delivery, A/V quality, visuals, and narration, you are the best teacher on TH-cam bar none. Furthermore, your lessons and courses yield excellent, professional results. I look forward to your Thursday morning installments and have watched your accounting videos since they began a couple of months ago. Great perspective. Thanks many times over.
Thank you for the kind feedback William.
You're an angel. This is very useful at work as we always taking data from NAV system to excel. I always face this problem, now found the solution .
Great.. this was exactly the answer which I was looking for. Explained in detail but simple and powerful way. Thanks a lot. May you be showered with all peace and prosperity.
Please visit channel "off to office" to know everything about text to column at one place and not in bits and pieces in different videos.
The way you explore the excel is just awesome. Keep doing 👍🏼...
WOW! the tutorial is unique. No one that needs office softwares' help and will come to here or see ur expertise that won't be tempted to click the subscribe button. Thanks the video helped me.
I'm glad to hear that!
Leila, you rock! I've tried all the quick and easy ways of solving this issue and all failed. Finding the right character did the trick. Thanks a ton! Subscribed!
Leila, you have no idea how long i have suffered from this spacing issue specially with numbers! you are going straight to heaven for making our life easier :) THANKS A LOT. i have been following your channel & becomeing a huge fan.
Happy to help 😊
Yeah, man! Leila is amazing. I know when I have an excel issue, her video will go deeper than others and not only resolve the problem but teach it to you.
I just used the formula.... Thank you Leila! You're Godsent!
Bundles of video out there on youtube, but this one covers the true problem statement and solution. Appreciated. Keep sharing such videos.
Thanks, will do!
You are a blessing in disguise. Much love from dubai!
Wow Leila.. It is soooo helpful. I was just wondering how to remove 160 spaces but here we go again u helped me alot like other issues. Thank you so much😘
I have learnt lot of Excel from your videos. You are amazing!
Olla!
Tu es toujours au top Leila, quel bonheur de suivre tes vidéos sur Udemy, en plus de celles de youtube bien sûr !!!!
Thank you so much. Your tutorials are so useful, very easy to follow and understand.
I had this challenge this week and I was stuck. It took me a while to figure it out. Thank you so much Leila.
Please visit channel "off to office" to know everything about text to column at one place and not in bits and pieces in different videos.
Another "eish" moment video. Never seen before. Short, snappy and on point
Please visit channel "off to office" to know everything about text to column at one place and not in bits and pieces in different videos..
You are my hero! Leila G. I asked Google how to get rid of spaces in Excel and you showed up and solved the problem.
Happy to help :)
Great Tutorial Leila! This solved the issue i was dealing with perfectly !!
thanks for explaining this. spent easily 60 mins trying to fix an issue in my excel sheet.
You're so beatuiful and your tutorials are amazing! Thanks for your sharing!
Thank you Leila. It's great tips that I need for my data work
Thanks Laila for the detail of this problem. And thanks again for sending me this solution few months earlier 🙂
Bravo! :). Well done, always going the extra mile. Great tips
Great!
I was looking for how to remove spaces from a value and found the solution.
Thanks Leila
This video changed my life right now 🤩 This is very helpful. Thank you so much!! 😇
Thanks for this. It’s one of the common difficulties I had to deal with. Now I know a great solution. You’re amazing!!!
Glad to help Jean.
Char(160) did the trick. I know that I need to read more about it. Amazing. Thanks
Spent hours to find the solution! Thank you!
Glad it helped!
Very useful dear sister
I watch all of your video and you are the best leader for me
Wow thank you so much....I was having this problem and I couldn't figure that out....and I was thinking I was missing something somewhere....thank you to understand our problems
As usual you are amazingly explained this. Thanks.
That = value ( substitute...) function is life saving, thank you!!!
Happy to help!
Thank you very much Leila, great tip. I do have a little one too when it comes to character spaces with numbers, what I do is select the space copy it and use the Ctrl+H to replace that space with "nothing and it works most of the time
Find and replace helps most of the time. Please visit channel "off to office" to know like methods of cleaning raw data and everything about text to column at one place and not in bits and pieces in different videos.
The way you alwaaaaayyyyyyyssss save my life. Only GOD can pay you back. I was trying to fix that for months. I was reduce to find a VBA code until you come up with the LIGHTS. The only Excel Angel on earth :)
I'm happy it's helping you :)
Fantastic tip. Will save a lot of work in the future. Thank you.
One of the greats tips I've had this year
That's helped clear up some annoyances I've had before copying data from / referring to another spreadsheet. Many thanks.
Glad it helped, John!
Thanks for this video!!! it helps a lot and save me time wasted trying to copy info from mail.
This was a very helpful, insightful video. Value added. Glad I subscribe to your channel.
Never encountered that issue!! But simply great to know this ASCII!!!
This video just saved my tons of time. Love you so much ❤️
Thanks! I always learn something new from your videos!
You are indeed great. Stay Blessed
Very useful video, you're simplifying our day to work life... Thanks a lot...
Thank you so much😊👍, I'm looking for this kind of videos.
Thank you, for the last two years I've had to put a helper column in to process a column and this explains why , thanks
No more helper columns needed :)
You can use sumproduct function instead ctrl shift enter to
Great Teaching style all videos are awesome.Thanks Leila
Where has this video been all my life!
These are great Excel solutions to really annoying issues that do happen. I would likely use Power Query if I had to repeat the process more than once. Thanks Lelia
Agree - Power query would be the optimal solution if the data set was organized properly.
beautifully explained laila. I've finally found a solid explanation to use CHAR function.
Glad it's helpful.
That's brilliant. I think you have just saved me an hour a month! Thank you.
Glad to help.
All i need to say.....u are just awesome mam....& thank you very very much...
Leila crushed it again! Thanx for the tip!
I really thanks to you for this video. First time I found a solution to this problem. 2 years back I had to write a VBA script about 80 lines
to solve this. I could not figure it out to solve this in excel.
Very useful video, I did face same problem numerous occasions at a time paste some data from other sources, I do know these are not numbers rather a text I try to resolve it as using tools as paste special as number but all in vain now I have new options as trim , thanks a lot for your assistance.
I was wondering why the TRIM function didn't remove all the unnecessary spaces and now I know why. Thank you Leila!
Happy to help Keisha!
Thanks for this. It took me 2 hours today to realize this as SUMIFS was not working. Wish I had watched this beforehand.
As usual one stop solutions to all my excel problems.. 🎉Thanks Leela
You're welcome 😊
Thank you so much. It helps me on the trim failure which bothered me since long time ✌️☺️
You're very welcome :)
This is amazing. I recently was doing an ebook question and when I copied all of the data over to excel there were many spaces and I couldn't do my calculations. Thanks so much for this video, I was able to fix the numbers!
Please visit channel "off to office" to know everything about text to column at one place and not in bits and pieces in different videos...
this is super helpful....was skeptical at first but it worked...my character was not 160 but 202....helped me identify that using your video!
Great to hear!
Awsome.. i always have data to copy from email to excel and face such problem.
Thanks now my problem solved
Excellent work Leila. Keep it up.
TRIM ALL function is a great idea!
Btw, for this kind of cleansing, Power Query does an amazing job! :)
Very true :) Power query rules!
THANK YOU SO MUCH! Great video, saved me so much time!!!
Excellent as usual! Thanks Leila 😃
Thank you so much! Couldn’t figure it out until the code portion, ours was code (9) and once we figured that out, piece of cake!
Yet another useful everday tip! Thanks!
That is what I needed! Thank you Leilia :)
Thanks for your video, and great solution. This behaviour has flummoxed me in the past!
Please visit channel "off to office" to know everything about text to column at one place and not in bits and pieces in different videos...
I came across the 160 code today for the first time.. thanks soooo much!!!
You're very welcome!!
As usual, blew my mind. thank you!!
Very well explained.Thank you 🙏
Excellent! Although SUBSTITUTE did not work for my specific issue i found that pasting code 160 in "Find and replace" solved my issue. Also I can see now that it's exactly what earlier commenter Thiago suggested.
THANK YOU!! I was fiddling around forever trying to make sense of Thiago's explanation (still a great suggestion but wasn't simple enough for my basic excel knowledge lol). Super glad I found your comment, this made sense to me and worked so I wasn't losing the other spaces.
This video saved a ton of my time... i was ripping my head because of this issue... thank you for the explanation.. really helped..
Great to hear!
great trick, thank you very much, all you videos are briliant
I learned a lot about excel from you. Thanks
I just used this function to solve a problem with my data, thanks Leila.
Glad it helped!