6 Ways to Remove Extra Spaces from Text in Excel
ฝัง
- เผยแพร่เมื่อ 28 พ.ค. 2024
- MY FULL EXCEL VBA COURSE [35% Discount]: www.teachexcel.com/vba-course...
200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
Remove spaces from the start, end, or middle of a cell in Excel - this includes removing all or just the extra spaces using formulas and other methods that can quickly replace spaces wherever you need them replaced in the worksheet.
Excel File: www.teachexcel.com/excel-tuto...
Excel Forum: www.teachexcel.com/talk/micro...
This tutorial covers 6 different ways to remove spaces including:
- remove spaces from the entire worksheet or workbook
- remove leading or trailing spaces
- remove specific instances of spaces (2nd or 3rd or 4th instance, etc.)
- remove middle spaces
- remove double spaces
- remove spaces using formulas or from a selection of cells
Many of the methods shown in this tutorial will also work for replacing other characters, besides spaces, and this allows you to create more versatile and usable formulas for your worksheets.
I hope you enjoy this tutorial :)
TeachExcel.com
#excel #tutorial #howto
MY FULL EXCEL VBA COURSE (Beginner to Expert) [35% Discount]: www.teachexcel.com/vba-course-update.php?src=yt_pinned_comment_axZj8Irt5W8
200+ Video Tutorials - 200+ Downloadable Excel Reference Files - 50+ Hours of Content
Thank you you have just saved me hours of work keep video great I had 196 rows to take . out and spaces you really saved me time again thank you
Thanks a lot for your help 🙏
A very complete tutorial. Thank you!!!
You are very welcome! I'm glad you think so! :)
Long-time viewer, first-time commenter. 🤣
Nice manner of describing the formulas so they're simple to understand and adapt. I can tell you're an old school Excel user from before the Ribbon. Your comments and observations about the quickest or easiest way to remember how to use shortcuts sound like they're coming straight out of my internal dialogue. Not having easy to use shortcuts in other applications drives me nuts, and I've started using AutoHotkey to create my own. Even in Excel, sometimes it's difficult to quickly format the font color or fill of a cell (without Visual Basic), so I have to write macros. Sometimes easier with AutoHotkey than with the internal Macro recorder / VB editor.
hahaha thanks for the comment!) i'm glad you like how I talk about the formulas, I never want anyone to feel left behind, just be careful watching the 30 minute+ ones as those might make a regular user sleep hahah. And, yea, shortcuts, it is one of those things that once you get started with them, you start to wonder how much of your life you lost before using them!!! AutoHotkey is a godsend! I downloaded it last year and now my only worry is getting on a computer that doesn't have it - this kind of software should be standard on every copy of windows in my opinion!
Super analytical and helpful video.. Bravo!!!!
That is really useful you are teaching me more than you will ever know. I am also trying to learn SQL and your explanation between the two talking marks now makes more sense.
I am very happy that I can help you Kathy :)
Very helpful explanation. Thanks!
Thanks for removing spaces video. Very useful to me. Please make videos these kind of tricks. So that I would expert in excel
I'm very glad that you like the video! Give me some ideas about what you would like to learn! I love making videos like this but they don't seem to be as popular as some more basic single function oriented videos, so I'm open to suggestions)
Very helpful. explained all types of spaces deletion.
Wow! Thanks a ton. I always struggled with this space pre ans post issues. This gives me a lot of relief. Thanks alot. You are the best!
Just an amazing and the best video about extra space.
Great video. I have always used Ctrl H. Trim function looks handy the substitute function looks invaluable. Thanks
Honestly, that is a really good way to do it without much fuss. Props to you for remembering the Ctrl + H shortcut, or maybe I am the only one who can never ever remember it haha
I dint even watch he complete video, I saw TRIM Function in your comment and tried it, it's exactly what i needed
Great video.. lots of good ideas in there. Thanks for sharing these tips and tricks. Thumbs up!!
Thanks a lot Wayne! By the way, the new Excel 365 formulas are slated to start up in a week or two, so hopefully, those will be interesting too)
I LOVE YOU. YOU ARE SMART
I find this very helpful especially in countif formula :)
Thanks you for upload it, it’s very helpful to me.
Now please upload for changes of date format from different formats.
Thank for this
I don't know about len formula which i found in ur video. Thanku very useful formula for me
I'm glad I could introduce you to it! Try and remember this one because it has many nice uses, especially when things get complex!
thank you very much
I clicked on Description link but no excel file get download but take me in to different page but I did not get the file
That was awesome. Thanks
Can you do this with the web version? It’s not really working on the web version for me.
Great! Thanks a lot 🔥
Great stuff!
Hello can you tell me why the trim function will not copy down? Instead it reads the same word down the column. Thank you
Can we fixed the second word of the name at specific intervals of that cell of that column like name and surname of student's
Thank you
Thanks sir very helpful video
I had a heart attack a year ago and have been too ill to work. Excel is my bread and butter and your videos help to keep my skills current. Also, it's always best to have several methods that achieve the same or similar results. Thank you for doing what you do.
You are very welcome! And I am very glad to hear that you are keeping yourself busy and alert! I hope that you will be better soon and I am very happy that I am able to help!!! If you ever have any suggestions on things you'd like to see, feel free to post a comment any time under my most recent video so I can notice and give it a reply! (sometimes I don't notice comments on older videos) Oh and if you want some fun in Excel, check out my Christmas Tree worksheet, that one always gave me a chuckle - maybe an update to a Pumpkin version is in order haha. I hope you have a great and healthy week!!!!
Can we fixed the second word of the name at specific intervals of that cell
Thank you 💖💖💖
I want formula how to give space between these words for e.g. johnrosemark, which formula is using to give space for these words, please reply
This didn't work for me but it got me literally copy and paste the trailing space into an ASCII analyser after which I found out that my space was actually a CHAR 160 (different kind of space to normal spaces), which is why trim wasn't working. So to use the trim function you need to find and replace all the char 160s with a normal space first! Hope this comment helps someone!
Thank you so much❤️❤️
Tell me how to add
I have Excel 2016 (MS Professional Plus 2016) - the TRIM function doesn't work on mine. I have a word in Cell A1 ===>"Nalleem, Sajjida " with several spaces after the word Sajida - it won' trim; i'm getting the same len=30. I even copied the word onto your Excel formula - still no luck
Thanks
Hello Sir,
I want to remove the sapce at the end of emails data. But when I type =trimA(
In the second coloum it shows
**Name?**
Instead of email address.
Please let me know how to fix it.
Good bro 🤜🤜
how to fully remove space in regular text folder
Can you create xlookup with vba in earlier versions of excel?
Not directly, but you can make formulas that do a similar thing and I have 2 tutorials on this topic that you might like:
th-cam.com/video/zBpkTTy2ViA/w-d-xo.html
th-cam.com/video/zBpkTTy2ViA/w-d-xo.html
Also i will have an xlookup tutorial coming out soonish)
You're going to create an xlookup vba video? I understand how it works, I was just trying to create my own formula in excel 2013 if possible. I'll just use the formulas you provided
Yea it will be released soon, but look to the other links I gave you and you will see how to do it for Excel 2013
Thanks you meri office mi koi help nahi kar raha tha thank you abhi mai kaam he kar rahi hu thank you
thanks for the shortcut. i always use ctrl +v, then ctrl v again LOL
To paste values? I am a little confused, though I haven't had my second coffee for the morning haha.
@@TeachExcel yes, it only work with newer version since office 2003 or 2010
Icing9 7799 666 how can you remove the spaces in above format. Could you please make a video.
Nope...Tried both and they don't work for my issue,.. Try this: (31.25 ) [space after 5] and use Trim and or Substitute...Neither work as they are both 6 charters long after...and still TXT ...thus still cant use in math's functions.
Try this guy (My Char 160 Tutorial) : th-cam.com/video/XwMN9FRD3Qk/w-d-xo.html
I think char 160 might be your issue.
How to remove the space in front of the words. Example: “ 18”
Great tutorial! unfortunately, I've not been able to make this work on a dataset I copied and pasted from the Web. Apparently there are other things that LOOK like spaces that TRIM, SUBSTITUTE or Replace just can't see. But I'm gonna use these tricks!
Thanks!
It's all well and good until a wild char(160) appears.
I heard they make firing squads legal now for this situation! hah
None of this worked. I have 7 spaces before a number.
i wanted to like this video but i didnt, lets keep it at that number
Thank you
You are very welcome Sujesh :)