I didn't know why the first four ways didn't work so I've been removing those spaces one by one (sometimes even 150 cells)........ And then I found this video with the CODE tip and it's working now. Thanks!
Non-breaking space in Windows is general across all Office applications and it was used mainly for keeping first name and last name for a person always on the same line. It also made sure that company names were expressed the same way.
With a large workbook and many sheets I need to simply find cells with strings that end with a blank space. It's crazy that Find and Replace works well for something like double space, lets you see all the strings first before you do a single replace or replace all but no regex or expressions to simple find a character at the end of a cell/string.
Last formulae is great but what if we have different number of spaces for each cells. By doing -1 it will delete one space and how we can do for more than one..
For me Trim function is obsolete.😅 Try using a CLEAN function😏. The results is awesome especially when importing the whole bank statement in one go😁. But I really like that macro techniques of your 😏
Hello Jon, thanks for your video. How to remove blank spaces in an excel file with huge data. It's not possible to remove one by one. Pls help with any video or suggestion. Thanks
Hi Ankit! 😊 Copy the new data and then use paste values to paste the data over itself. That will replace the functions with editable data. I speak about this around the 4:06 mark in the video. Hope that helps! 🙂
I had an issue where data from a csv file from labware (.xls file from excel 2007!!!) had "ghost" line feeds that didn't have a ASCII code. I used clean function to get rid of them. I need to get a six digit value from a string preceed by a # sign sometime with a space before sometimes not. I use clean and replace with a "for each" in a vba macro on a column range.
How to remove space from a date and convert it into number format. I used =substittue (A1,char(160), "")*1 however space gone but still it didn't convert into number
(10:00) I was hoping you would introduce the CLEAN(TRIM()) combo. Old timers like me know these tricks when we had to deal with mainframe output and there were many characters that needed to be removed before MS-Excel could use the data. This combo will bet a lot of unwanted junk in strings but not all. And also, the VBA can also be used to specifically target a specific character to remove. But I am a new subscriber so… let’s see more! Thanks!
I wan't a help from you. There is a problem in excel in my computer. How to fix when one column automatically copy in every sheet of the work book. Its automatically happening when we enter smthing. I couldnt fix it. So If u can plz help me.
I believe Microsoft 365 is the best option because of all the great new features and updates that Microsoft is rolling out on a monthly basis. I've covered a lot of these on our channel including Dynamic Arrays Formulas, XLOOKUP, LAMBDA, and more. Excel 2016 is still a great version. It just won't have all the new features that can really help save time data analysis. The license keys only work for their specific product or year version. I hope that helps. Thanks again and have a nice day! 🙂
This video saved my project - thank you!
Great Tips,Have Had Many A Headache With Unseen Spaces..Thank You Jon :)
Thank you Darryl! I appreciate your continued support. 🙏
Hi Jon. Great video! Thanks for the excellent tips and tricks on how to locate and remove blank spaces in text. Thumbs up!!
Thank you Jon for your hard work and for sharing this. I try always not to miss any of your videos
This was a good refresher on space hunting. I always have to google it as it doesn't happen that often. Thanks!
I didn't know why the first four ways didn't work so I've been removing those spaces one by one (sometimes even 150 cells)........ And then I found this video with the CODE tip and it's working now. Thanks!
Your tip is just in time. Thank you, Jon.
You are a great teacher. I have learned a lot fom you.
thanks Jon, more great tips
Thank you Peter! 🙂
Non-breaking space in Windows is general across all Office applications and it was used mainly for keeping first name and last name for a person always on the same line. It also made sure that company names were expressed the same way.
Thanks!! Saved me a TON of time! And aggravation!
Last one is a life saver.
Excellent dear
thanks jon.. more power to you and to your team!
Thank you Denky! 🙂
Beautiful clarification!!!!!
what an awesome tutorial. Thank you !
Glad you liked it, @alexandrelow8789 😀
really love your list search it useful on excel but we need on MS access too.
I kinda spaced out towards the end, but overall great content as usual!
Haha thanks Om! 😃
With a large workbook and many sheets I need to simply find cells with strings that end with a blank space. It's crazy that Find and Replace works well for something like double space, lets you see all the strings first before you do a single replace or replace all but no regex or expressions to simple find a character at the end of a cell/string.
Thank you very much.
Last formulae is great but what if we have different number of spaces for each cells. By doing -1 it will delete one space and how we can do for more than one..
Thanks for the video 😍
Thank you very much....❤❤
You're welcome 😊
For me Trim function is obsolete.😅
Try using a CLEAN function😏. The results is awesome especially when importing the whole bank statement in one go😁.
But I really like that macro techniques of your 😏
Hello Jon, thanks for your video. How to remove blank spaces in an excel file with huge data. It's not possible to remove one by one. Pls help with any video or suggestion. Thanks
Super, Thanks so much!
Happy to help! 😀
I did use trim function but now from the new data I wish to remove the function so I can edit the data. If that could be mentioned it would be great !
Hi Ankit! 😊 Copy the new data and then use paste values to paste the data over itself. That will replace the functions with editable data. I speak about this around the 4:06 mark in the video. Hope that helps! 🙂
(10:00) I was hoping you would use the CLEAN(TRIM
I have Code 30 spaces that I can’t seem to remove. I cannot copy and paste it into the find and replace function. Any other suggestions?
I had an issue where data from a csv file from labware (.xls file from excel 2007!!!) had "ghost" line feeds that didn't have a ASCII code. I used clean function to get rid of them. I need to get a six digit value from a string preceed by a # sign sometime with a space before sometimes not. I use clean and replace with a "for each" in a vba macro on a column range.
Thanks for sharing, Michel! 👍
How to remove space from a date and convert it into number format. I used =substittue (A1,char(160), "")*1 however space gone but still it didn't convert into number
(10:00) I was hoping you would introduce the CLEAN(TRIM()) combo. Old timers like me know these tricks when we had to deal with mainframe output and there were many characters that needed to be removed before MS-Excel could use the data. This combo will bet a lot of unwanted junk in strings but not all. And also, the VBA can also be used to specifically target a specific character to remove. But I am a new subscriber so… let’s see more! Thanks!
Hello sir
Its showing error when i used trim function ref# what is it mean
Gotcha
I am not saying paste values i am pasting directly
I wan't a help from you. There is a problem in excel in my computer. How to fix when one column automatically copy in every sheet of the work book. Its automatically happening when we enter smthing. I couldnt fix it. So If u can plz help me.
I have few doubts:
1. Is it possible to activate office 2019 with office 2016 key ?
2. Or Is office 2016 more than enough in 2021/2022 ?
I believe Microsoft 365 is the best option because of all the great new features and updates that Microsoft is rolling out on a monthly basis. I've covered a lot of these on our channel including Dynamic Arrays Formulas, XLOOKUP, LAMBDA, and more. Excel 2016 is still a great version. It just won't have all the new features that can really help save time data analysis.
The license keys only work for their specific product or year version.
I hope that helps. Thanks again and have a nice day! 🙂