Hi, this is Wayne again with a topic “Using the Trim Function in Excel”.
In this short tutorial, I want to show you an excel function. That is a little-known function, but I find it to be pretty useful and it is the trim function. So here we have in this spreadsheet a list of movies. We have the title of the movie category rating and the year it came out, but this spreadsheet was sent to me by someone else and any time you’re sharing spreadsheets with someone when you’re receiving someone else’s spreadsheet, there’s always a risk that when you get their spreadsheet, That there will be some mistakes and some errors, and you can see here for some reason, this spreadsheet comes with way too many spaces. I have blank spaces at the beginning of some of these entries.
I have extra spaces in the middle of some of these entries and also at the end of some of these entries. There are some extra spaces you can see here. The movie elf, it looks like has four extra spaces at the end, and so I would like to clean up the data in this spreadsheet, but I don’t want to have to click in each cell and hit the backspace button to fix those mistakes. That’S gon na be a lot of work, so instead, what I’m gon na do is simply insert a new column, so I’m going to right click on column B and then click insert, and that gives me a brand new column and it pushes the data that was There to the right and I’ll just create a formula so I’ll click in cell b2 and I’ll start my formula the way I always do with an equals sign and then the function trim left parenthesis and then I’m just gon na simply click on cell a2. Now, of course, I could have just typed a2 in the cell so either way either type it or click on the cell. I’M supposed to put a right parenthesis there, but I don’t have to so I’m not going to in this case I’ll tap, enter on the keyboard and look what it did.
It cleaned up this record that had extra spaces at the beginning of the entry and also in between these two words: it’s trimmed out those extra spaces and it looks just the way I want it to look so now. I need to go to b3 and do the same thing again right. Actually, no, I don’t have to do the same thing again and again and again, all I have to do. Is click on cell b2 go to the lower right hand, corner of that cell and, as you can see there in the lower right corner, we have a little green square. That’S called the autofill handle and I’m gon na put my mouse on the autofill handle until the mouse pointer turns into a black plus sign and then I’ll just click and drag and pull that down all the way to the bottom of my data and then look What happens it cleaned up all of the data, all the way up and down column a this is exactly how I want the data to look no extra spaces. Now I have a problem, though, of having duplicate data. I have column a and I have column B and when you do this, you might be tempted to just delete column a but watch what happens if I right click on column, a and delete everything? That’S there, it ruins what was column B. You can see it moved it to the left and it ruined the data.
Why? Well? Because this data relies on this data. Cell b2 is a formula and you can see that formula if you double click on it or look here on the formula bar, and so this cell relies on cell a2. When I delete the contents of cell a2, it ruins to the formula. So I seem to have a problem here.
Well, there is a solution. I can right click on column B and simply copy and then I’ll right-click on column, a and paste special. When you click on paste special. It gives you some options. I want to paste it not as all not as formulas but as values, so I select that click. Ok and now I have a pasted version of this data, but if you double click on it, you can see that the data now in column a is not part of a formula.
It doesn’t depend on any other cell or column. So at this point I can right, click on column B, delete it and my data is fine, so the trim function is a great way to clean up data, but, as you can see, there are some steps involved. Knowing how to use this trim function has helped me several times, and I hope that you found this tutorial to be helpful. If you have please click the like button below and consider connecting with me on. My social media accounts like Facebook, Pinterest and Twitter, and definitely do subscribe to my youtube channel for more videos about technology for teachers and students and when you do subscribe, please click the bell next to the subscribe button.
If you do that, you’ll be notified. Whenever I post another video and watch for another video from me at least every Monday, if you’d like to support my youtube channel, consider becoming a supporter of mine through my patreon account and you’ll find links to that in the description below .