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 :)
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.
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!
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 !!!
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!!!
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.
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!
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.
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.
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.
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.. 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.
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 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😘
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!
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!
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.
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 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, 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!
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!
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.
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
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()👍
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
Thank you so much leila.....for this solution ....i suffer lot while fetch datas into my model its totally messed up .....i think now i got a solution for this by you........really its help me ...thankyou so much.....
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 👍
Dear Mam, Finally today IAm understood this video content & concept, &I have use this trick for practical work . It's very unique 😊 and powerful method . Thanks for giving us this knowledge ❤️🙏🙏 I hope you will provide all of us more than this... In Future 😉 Again a large thanks to the Dept of my heart..
Great video! It helped me learn how to clear all blanks from a text string. Thank you Leila! However, I noticed the approach shown here to clearing blanks from data cells with number strings seems a bit complex. I was shown years ago to simply multiply the cell in question by 1 (i.e., C9*1), then the result is expressed as a value number. Any issues with this approach? Keep up your mission, I appreciate it!
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 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.
Leila, you have just solved one of the peskiest problems I had and could never figure it out. Thank you however I usually change the cell from text to general or number and it usually does the trick unless I am missing something. :)
Thank you for the introduction to the TRIM, Substitute and CODE functions!!! Another way to force a number corrupted with spaces into becoming true number, is to simply multiply the cell by one.
Grab the file I used in the video from here 👉 pages.xelplus.com/trim-spaces-file
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 :)
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.
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.
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 :)
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 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 !!!
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!
This is an incredible tutorial video. It is so refreshing seeing women, attractive at that, competently talk about technical stuff
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.
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!
Hi Leila, I just bought your course on Udemy. You are a mentor for me! I will study hard and practice!
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.
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.
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.
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!
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.
The best video. Solution to my problem when I convert PDF to excel. Thanks for sharing your knowledge.
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.
thanks for explaining this. spent easily 60 mins trying to fix an issue in my excel sheet.
Very useful dear sister
I watch all of your video and you are the best leader for me
Straight to the point. I've been searching this method for an hour. Thanks for the tricks!
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!
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.
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.
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!
All i need to say.....u are just awesome mam....& thank you very very much...
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 .
I know about Trim(). But the explanation you have given, has never seen before. thankyou.
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 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.
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...
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!
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!
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.
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!
Char(160) did the trick. I know that I need to read more about it. Amazing. Thanks
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??
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 🌟
Thank you so much. Your tutorials are so useful, very easy to follow and understand.
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!
As usual one stop solutions to all my excel problems.. 🎉Thanks Leela
You're welcome 😊
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..
Thanks for this. It took me 2 hours today to realize this as SUMIFS was not working. Wish I had watched this beforehand.
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!
That's helped clear up some annoyances I've had before copying data from / referring to another spreadsheet. Many thanks.
Glad it helped, John!
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.
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. 👍
The way you explore the excel is just awesome. Keep doing 👍🏼...
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.
That = value ( substitute...) function is life saving, thank you!!!
Happy to help!
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()👍
Spent hours to find the solution! Thank you!
Glad it helped!
Awsome.. i always have data to copy from email to excel and face such problem.
Thanks now my problem solved
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 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
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 came across the 160 code today for the first time.. thanks soooo much!!!
You're very welcome!!
This video changed my life right now 🤩 This is very helpful. Thank you so much!! 😇
Thank you so much leila.....for this solution ....i suffer lot while fetch datas into my model its totally messed up .....i think now i got a solution for this by you........really its help me ...thankyou so much.....
Hope it will help.
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 👍
Dear Mam,
Finally today IAm understood this video content & concept, &I have use this trick for practical work .
It's very unique 😊 and powerful method .
Thanks for giving us this knowledge ❤️🙏🙏
I hope you will provide all of us more than this...
In Future 😉
Again a large thanks to the Dept of my heart..
It's my pleasure :)
I just used this function to solve a problem with my data, thanks Leila.
Glad it helped!
Thanks for this video!!! it helps a lot and save me time wasted trying to copy info from mail.
You are awesome. Have done a few of your courses. The clarity in your explanations is of highest order.
god, woman, i love you! thank you for your smartness
Learning never ends. Leila, your videos inspire to learn more and more...Keep this up.
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
Damn, this works!!!!!!!!!!!!!!!!!!! I think this is the most useful video I have seen on TH-cam!!!!
This video just saved my tons of time. Love you so much ❤️
Great video! It helped me learn how to clear all blanks from a text string. Thank you Leila!
However, I noticed the approach shown here to clearing blanks from data cells with number strings seems a bit complex.
I was shown years ago to simply multiply the cell in question by 1 (i.e., C9*1), then the result is expressed as a value number.
Any issues with this approach?
Keep up your mission, I appreciate it!
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 ❤️❣️
beautifully explained laila. I've finally found a solid explanation to use CHAR function.
Glad it's helpful.
You're so beatuiful and your tutorials are amazing! Thanks for your sharing!
Great!
I was looking for how to remove spaces from a value and found the solution.
Thanks Leila
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 just used the formula.... Thank you Leila! You're Godsent!
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...
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.
Thank you so much. It helps me on the trim failure which bothered me since long time ✌️☺️
You're very welcome :)
Normally I use text to columns option to remove those extra spaces, but still this one really good one
Very useful video, you're simplifying our day to work life... Thanks a lot...
what a great video, it helped me significantly to clean data. Thanks
Glad it helped!
Great Teaching style all videos are awesome.Thanks Leila
Where has this video been all my life!
Not all heroes wear a mask!
Thank you for servicing the excel community.❤
Our pleasure!
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.
This video is solved my most of problems 😂😂
Great Work.................Heads of you.
Glad it helped
@@LeilaGharani can I got your contact numbers.
You solved a great problem from my life! Thank you very much for this video !!!
Glad it helped!
Thanks Laila for the detail of this problem. And thanks again for sending me this solution few months earlier 🙂
One of the greats tips I've had this year
Leila, you have just solved one of the peskiest problems I had and could never figure it out. Thank you however I usually change the cell from text to general or number and it usually does the trick unless I am missing something. :)
The video itself started at that point, where my knowledge of trim ended.
Thanks for the great video
You're welcome!
Fantastic teaching skills. Knows her stuff and how to explain it!
Thank you! 😃
Never encountered that issue!! But simply great to know this ASCII!!!
As usual you are amazingly explained this. Thanks.
Thank you for the introduction to the TRIM, Substitute and CODE functions!!! Another way to force a number corrupted with spaces into becoming true number, is to simply multiply the cell by one.
You're welcome Michael! The multiplying works with normal spaces, but unfortunately not with non-breaking spaces....
Your video saved me weeks of work! Thanks a lot!!
You're very welcome!