You can use text functions to help edit your data in a way that's gonna be more efficient than manual data entry. We'll take a look at four different text functions here. Very simple to explain, three of them deal with the case of the text that you're working with.
Text Functions
With casing, for instance, you may run into a situation where you need to capitalize the first letter of every word in a cell and you'll use the proper function. you may run into a situation where you need to capitalize all the text in a cell, you'll use the upper function short for uppercase. you can probably imagine what the lower function will do. it'll take all the text and turn it to lowercase. Finally, we'll take a look at the trim function. This has nothing to do with the casing of your text but it does have to do with removing extra spaces that may be in a phrase in your Excel cell.
PROPER
So I'll start by showing you how this works with four quick examples and then we'll take a look at the exercises that students would usually work with in class. I'll start with proper. I'll head over to that cell and here to the left underneath the word proper in the gray cell. to the left I have the United States that's not properly capitalized so I need to properly capitalize it. now I'm going to show you the Brat best practice. when it comes to typing in a function, you'll type in equal then you'll type in the name of the function but you'll start by typing the first few letters P R this is Auto completing the name of the function. now I typed in PR o P and I see proper selected. what I usually say at this point is unless you like typing what you should do next is press tab because that will complete the name of the function and even put in the open parentheses so you don't have to press shift above the number nine to type in that open parenthesis. it's almost like a keyboard shortcut. now the cell that I want to select. I usually ask students is it on the left or is it on the right they say the left. without using my mouse I can use the left arrow key that selects the text without even using my mouse. in order to complete this function, I don't have to press ENTER.
UPPER & LOWER
I need to go over to the right so I can make sure that USPS is all in uppercase. the keyboard key I'm gonna use to complete this entry and I don't have to put in the close closing parenthesis because if it's a function where there are only two parentheses necessary I can leave it out and Excel has my back.
I'll make sure to add the parentheses there I'll press tab that now is United States properly capitalized I'll head over to USPS I'll type in equal the first few letters for the upper function UPP that's the minimum I need to type and then I'll need I can press tab left arrow and then press tab again and that will capitalize USPS. now return to sender that's yelling at me I need to lower the volume so literally I'll type in equal and then L o W and I'll press tab left arrow and then I'll press tab again. that makes all the text lowercase.
MOUSE
now just for people who like using the mouse, I'll show you how you could do this with the mouse. I need to remove the extra spaces between New York and the zip code so I'll type in equal T R I M the I'll press shift and above the number nine put in the open parenthesis then I'll take my mouse and click on New York and the zip code then I'll press ENTER. that removes all the extra spaces from the cell but keeps the space that's necessary to separate New York and the zip code. Those are four very useful text functions that you can use when you're working with text and data entry in your Excel spreadsheet.
More Exercises
I'll quickly go through these next few exercises. We increase the level of difficulty for our students by adding in more rows of data but with autofill, this will be a piece of cake. I need to properly capitalize all the names in this list so I'll type in equal PROP tab left arrow and then press ENTER. now instead of dragging this down, I have another option. I can double click on the black plus sign and this will fill all the values down as far as the adjacent column. It uses that as a guide to know when to stop. I'm gonna go to upper I'm gonna type in equal you P tab left arrow press Enter if I want I can select the first cell and that has the formula and the blank cells underneath and then press control D. that is the keyboard trick that we can use I could also select the cell using the shift key and pressing on the down arrow key select the entire range. when I get to the bottom I'll press control D so now I want to lower the volume for the emails. emails are not case-sensitive so the maze will all be lowercase equal L o W left arrow press ENTER and then here I'll use the autofill handle and then just drag it down then. Finally, I want to remove the excess spaces from these cells.
Keyboard Shortcuts
Now some of you are saying is there a way that you could just do this using a keyboard shortcut. it's a little difficult but there is a workaround. I'll type in equal TR. I tab left arrow and press Enter. now if I want to select the range that starts from FedEx and goes all the way to the bottom I could use a keyboard shortcut control shift down. that may select that column and stop right at the USPS field in the adjacent column. if I press the control shift down that doesn't work because that's like a bottomless pit it goes all the way down so I could press control shift down. this is what I was hoping would happen but that's not gonna happen so here's a workaround. you'll press ctrl C to copy that value then you'll press the left arrow to go to the adjacent column. you can use a keyboard shortcut control down arrow to get to by the bottom to get to the bottom of the range. now you're exactly where you need to be but one cell over so I'm gonna press the right arrow and this is where I wanted to get to. now I want to select everything from the bottom up to what I just copied so I'll use ctrl shift up and then I'll press control V that will paste all the formulas in that column just using keyboard shortcuts. then I'll press escape and my work is done. These are four text functions that you can use to quickly edit the casing of text in your data as well as remove spaces.