Remove ALL Unwanted Spaces in Excel (TRIM ALL Function?)
HTML-код
- Опубликовано: 25 июл 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
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!
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!
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.
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.
The best video. Solution to my problem when I convert PDF to excel. Thanks for sharing your knowledge.
You are awesome. Have done a few of your courses. The clarity in your explanations is of highest order.
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 RUclips 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.
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!
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!
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.
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 !!!
I know about Trim(). But the explanation you have given, has never seen before. thankyou.
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 ❤️❣️
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.
Hi Leila, I just bought your course on Udemy. You are a mentor for me! I will study hard and practice!
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 :)
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.
Leila, you are a star. I have been struggling to find a youtube video that can fix this issue. You are an absolute star 🌟
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 .
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
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!
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.
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.
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😘
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..
Learning never ends. Leila, your videos inspire to learn more and more...Keep this up.
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 👍
This is an incredible tutorial video. It is so refreshing seeing women, attractive at that, competently talk about technical stuff
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!
Thanks Leila, you have always understand the problems before I encounter them.
The way you explore the excel is just awesome. Keep doing 👍🏼...
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...
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.
Char(160) did the trick. I know that I need to read more about it. Amazing. Thanks
That's helped clear up some annoyances I've had before copying data from / referring to another spreadsheet. Many thanks.
Glad it helped, John!
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!
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.
I just used the formula.... Thank you Leila! You're Godsent!
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.
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!
This video changed my life right now 🤩 This is very helpful. Thank you so much!! 😇
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
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 Laila for the detail of this problem. And thanks again for sending me this solution few months earlier 🙂
You're so beatuiful and your tutorials are amazing! Thanks for your sharing!
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.
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 :)
As usual you are amazingly explained this. Thanks.
My goodness .I spent hours to fix this problem.😭😭😭 Glad that I had watched your video and It worked . Thank thank thank thank you so much dr. .
Glad it helped, Nethra!
You are a blessing in disguise. Much love from dubai!
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.
I have learnt lot of Excel from your videos. You are amazing!
Great!
I was looking for how to remove spaces from a value and found the solution.
Thanks Leila
Hi Leila.. great tips. Before watching this video, I was under the impression that CLEAN would solve for CHAR(160), but it does not. Your use of SUBSTITUTE along with TRIM and or VALUE is perfect. As always, learned something new today from your channel. Thanks for all the tips, tricks and insights that you so generously share with each new lesson. Thumbs up!
PS - Note that if you user Power Query, Transform=> Format=> Trim will remove the space in the original data created by CHAR(160) but Transform=> Format=> Clean will not.. interesting!
Thanks for your feedback Wayne. I wish Excel's Trim function behaved that way too....
Trim does spaces and Clean nonprintable characters. Clean is great when you bring in stuff from the web that is full of all those weird characters and symbols that make no sense. You don't see them on the webpage but they show up in Power Query, Word, etc.
thanks for explaining this. spent easily 60 mins trying to fix an issue in my excel sheet.
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.
TRIM ALL function is a great idea!
Btw, for this kind of cleansing, Power Query does an amazing job! :)
Very true :) Power query rules!
I used TRIM(SUBSTITUTE(Value, CHAR(160),CHAR(32)) but you brilliantly skip that argument. Thanks! I'll try to write a VB function and tribute that to you. BTW, your Udemy VBA course helped me so much in understanding more complex codes at work.
If I have more than one space how I can remove??
Thank you Leila. It's great tips that I need for my data work
This video just saved my tons of time. Love you so much ❤️
It was very helpful! Nothing works, neither TRIM or SUBSTITUTE or CLEAN to remove the stubborn spaces! This combination is great! Thanks' a lot, it saves me great time and nerves!
You're very welcome, Claudiu!
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
Thanks for this. It took me 2 hours today to realize this as SUMIFS was not working. Wish I had watched this beforehand.
Thank you so much. It helps me on the trim failure which bothered me since long time ✌️☺️
You're very welcome :)
Great Tutorial Leila! This solved the issue i was dealing with perfectly !!
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!
Hi Leila, thanks for this. I tried Google Sheets and it was smart enough to quickly remove the blank space that Excel had great difficulty removing. I used "Find and Replace" in the edit menu to remove currency abbreviations and space in numbers I copied from my bank's website. I assume that Google Sheets' trim function is likely to work easily too.
beautifully explained laila. I've finally found a solid explanation to use CHAR function.
Glad it's helpful.
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!
Thanks for this video!!! it helps a lot and save me time wasted trying to copy info from mail.
Awsome.. i always have data to copy from email to excel and face such problem.
Thanks now my problem solved
That = value ( substitute...) function is life saving, thank you!!!
Happy to help!
Fantastic tip. Will save a lot of work in the future. Thank you.
All i need to say.....u are just awesome mam....& thank you very very much...
As usual one stop solutions to all my excel problems.. 🎉Thanks Leela
You're welcome 😊
Bravo! :). Well done, always going the extra mile. Great tips
Thanks! I always learn something new from your videos!
This was a very helpful, insightful video. Value added. Glad I subscribe to your channel.
I came across the 160 code today for the first time.. thanks soooo much!!!
You're very welcome!!
That's brilliant. I think you have just saved me an hour a month! Thank you.
Glad to help.
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...
You are indeed great. Stay Blessed
THANK YOU SO MUCH! Great video, saved me so much time!!!
Never encountered that issue!! But simply great to know this ASCII!!!
Great Teaching style all videos are awesome.Thanks Leila
Wunderschön Liebe Leila, wirklich nützlich, CHAR 160, zusammen mit TRIM gefällt mir sehr,
Viele liebe Grüsse 🤗 🌞
Freut mich! Liebe Grüsse 🤗
You solved a great problem from my life! Thank you very much for this video !!!
Glad it helped!
Where has this video been all my life!
I just used this function to solve a problem with my data, thanks Leila.
Glad it helped!
One of the greats tips I've had this year
Damn, this works!!!!!!!!!!!!!!!!!!! I think this is the most useful video I have seen on RUclips!!!!
As usual, blew my mind. thank you!!
Great trick...I faced Char(160) while copying table data from email body... It messed up my mind for quite a long until I cracked it about 2 years ago using SUBSTITUTE() and TRIM()👍
Oh lucky me... today morning only i faced same issue where trim didn't worked... fortunately i found this one when i really needed it .. thanks a lot mam
Great timing then :) Glad it's helpful.
great trick, thank you very much, all you videos are briliant