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.
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.
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!!!
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!
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 UA-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.
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.
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.
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!
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!
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 !!!
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 :)
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 ❤️❣️
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!
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.
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.
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.
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
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!
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!
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
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 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.
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.
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😘
Wow Amazing... However there is a better way... Copy the space and replace it ( find and replace option) with normal space and then use TRIM formula. Hope this is helpful..
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
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.
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.
Grab the file I used in the video from here 👉 pages.xelplus.com/trim-spaces-file
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.
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.
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!
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!
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 UA-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.
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.
The best video. Solution to my problem when I convert PDF to excel. Thanks for sharing your knowledge.
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..
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.
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!
Char(160) did the trick. I know that I need to read more about it. Amazing. Thanks
I know about Trim(). But the explanation you have given, has never seen before. thankyou.
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.
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!
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!
This is an incredible tutorial video. It is so refreshing seeing women, attractive at that, competently talk about technical stuff
As usual you are amazingly explained this. Thanks.
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 :)
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 !!!
Thank you so much. Your tutorials are so useful, very easy to follow and understand.
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 :)
That's helped clear up some annoyances I've had before copying data from / referring to another spreadsheet. Many thanks.
Glad it helped, John!
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!
Very useful dear sister
I watch all of your video and you are the best leader for me
That = value ( substitute...) function is life saving, thank you!!!
Happy to help!
Thanks Leila, you have always understand the problems before I encounter them.
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.
Hi Leila, I just bought your course on Udemy. You are a mentor for me! I will study hard and practice!
You are the best, smoothly, simple , choose the simplest way which we can understand easily, thanks a lot for your excellent work
Thanks for this. It took me 2 hours today to realize this as SUMIFS was not working. Wish I had watched this beforehand.
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 ❤️❣️
Leila, you are a star. I have been struggling to find a youtube video that can fix this issue. You are an absolute star 🌟
The way you explore the excel is just awesome. Keep doing 👍🏼...
You are awesome. Have done a few of your courses. The clarity in your explanations is of highest order.
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 video just saved my tons of time. Love you so much ❤️
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!
I normally use trim(clean( )) function, and it's working fine.
Agreed
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.
You're so beatuiful and your tutorials are amazing! Thanks for your sharing!
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!
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!
I came across the 160 code today for the first time.. thanks soooo much!!!
You're very welcome!!
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.
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!
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!
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!
As usual one stop solutions to all my excel problems.. 🎉Thanks Leela
You're welcome 😊
Great Teaching style all videos are awesome.Thanks Leila
Learning never ends. Leila, your videos inspire to learn more and more...Keep this up.
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...
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.
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...
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
I just used this function to solve a problem with my data, thanks Leila.
Glad it helped!
thanks for explaining this. spent easily 60 mins trying to fix an issue in my excel sheet.
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!
god, woman, i love you! thank you for your smartness
Damn, this works!!!!!!!!!!!!!!!!!!! I think this is the most useful video I have seen on UA-cam!!!!
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.
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
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 👍
Thank you Leila. It's great tips that I need for my data work
All i need to say.....u are just awesome mam....& thank you very very much...
Great!
I was looking for how to remove spaces from a value and found the solution.
Thanks Leila
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 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.
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.
Where has this video been all my life!
This video changed my life right now 🤩 This is very helpful. Thank you so much!! 😇
You solved a great problem from my life! Thank you very much for this video !!!
Glad it helped!
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😘
Normally I use text to columns option to remove those extra spaces, but still this one really good one
Thanks Laila for the detail of this problem. And thanks again for sending me this solution few months earlier 🙂
Fantastic teaching skills. Knows her stuff and how to explain it!
Thank you! 😃
You are indeed great. Stay Blessed
I just used the formula.... Thank you Leila! You're Godsent!
Very well explained.Thank you 🙏
Spent hours to find the solution! Thank you!
Glad it helped!
beautifully explained laila. I've finally found a solid explanation to use CHAR function.
Glad it's helpful.
Thanks Leila. Did not know about char 160.
Wow Amazing... However there is a better way... Copy the space and replace it ( find and replace option) with normal space and then use TRIM formula. Hope this is helpful..
There is always a short cut to the short cut. 👍
Your video saved me weeks of work! Thanks a lot!!
You're very welcome!
Thanks for this video!!! it helps a lot and save me time wasted trying to copy info from mail.
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.
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??
One of the greats tips I've had this year
THANK YOU SO MUCH! Great video, saved me so much time!!!
As usual, blew my mind. thank you!!
Thank you so much. It helps me on the trim failure which bothered me since long time ✌️☺️
You're very welcome :)
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.
Seriously, you are awesome, I learned something I needed for very long time, thank you so so much 😊.
My pleasure 😊
Never encountered that issue!! But simply great to know this ASCII!!!
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 have learnt lot of Excel from your videos. You are amazing!
Thanks! I always learn something new from your videos!
The video itself started at that point, where my knowledge of trim ended.
Thanks for the great video
You're welcome!