Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Some of the functions in your function Library designed to help format information that is residing in cells. And the next function that we're going to look at, the Trim function's going to do just that. If you take a look at the information that's in column B, you'll notice that most of my values seem to be just ever so slightly in between the beginning of the cell, and where my letters start. And the only reason I notice that, is because as I come down I see they're not lining up properly. So I'd like to fix that. Now, one way I could do is to go into the cell, and take the cell, and you know, remove all the leading spaces, click Enter, and then it lines up nicely to the far left of the screen. But even doing it 25 times is far too many times than I want to do it. So what's an easy way that I could remove the leading zeros on all that information? Well, I can do that by using the Trim function. Now again, let's review how you work with your functions. You place your indicator in the cell where you want the new answer to go.
You then go up to the category in the Function Library where that particular function resides, and I know that the Trim function is in the Text grouping. Now, you'll be hunting and pecking the first little while when you are playing around with the functionality, because you're not going to know where these are, but at least the categories give you an idea of where to look. So if you want to work with text, look under the Text category. If I scroll to the bottom, I find the Trim function, and I select that. So now I have a dialog box that comes up.
It gives me a description of what, actually, this function is going to do. So, I want to double check that I've picked the right one, remove all spaces from a text stream, except for single spaces between words. That's exactly what I want to do. Now, in this little search bar here, it's asking me what area do you want to perform this function on? So the easiest way to do that is to collapse the dialog box by clicking on the indicated icon, going over to your worksheet and selecting the first cell that you want that to be performed on.
Come back to your Function Arguments dialog box and re-open it by clicking on the icon, and you'll see it gives you an idea of what your outcome is going to be. So you can confirm before you even click OK on this function, whether or not you're in the right spot. So it's saying, it's taking Pastry with a leading space and going to return Pastry with no spaces. That's exactly what I want to do, so I click OK. And Voila! It's done. Now I'd like to do this for all of the rows in this column. I can repeat putting in that same function over, and over, and over for 25 rows, but that's not going to save me any time.
What will save me time, is to be able to fill the information down. So if I click on the grab bar in the cell that I'm working with, and drag that particular cell all way down through all 25 rows, and let go, that function is copied into each of the cells that you see here. So just by clicking and dragging, I have removed all of the spaces that preceded each of these different words. Now the final thing I'd like to do is I want to copy this information over into my Item Type column so that it's nice and clean.
To do that, I right click and say, Copy. I then go into the first cell, where I want this information to be pasted, I right-click, and I select Paste Special this time. I skip over the regular Paste and I pick Paste Special. Why? Once I pick up Paste Special, it asks me, what special pasting do I want to do? And in this case, because I am working with formulas, I don't want the formulas copied over, I want the value copied over. And what the value is is the final result.
So instead of putting in the Trim function that would bring over the value of Pastry, it'll actually bring the word Pastry. So you select Values from Paste Special, click OK, and now, in the column B2, you'll see Pastry without a leading space, and in C2, you'll see the function that got us there. To do one final clean up, I select the column where the function resides, I right-click and delete it.
Now I have a nice clean table to work with.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 80365 Viewers
80 Video lessons · 132732 Viewers
52 Video lessons · 66273 Viewers
59 Video lessons · 52048 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.