Enhance your efficiency in Excel by mastering essential text functions that quickly adjust capitalization and spacing. Learn practical tips to streamline data editing without manual effort.
Key Insights
- Utilize Excel's text functions—Proper, Upper, Lower, and Trim—to efficiently edit text data, helping capitalize, uppercase, lowercase, or remove extra spaces.
- Apply Excel best practices by typing the initial letters of a function and using the Tab key for faster input, as well as employing keyboard shortcuts and autofill to complete edits across large datasets.
- Practice exercises provided by Noble Desktop increase Excel proficiency by demonstrating advanced methods to quickly adjust text formatting, ensuring accurate and consistent data entry.
This lesson is a preview from our Excel Bootcamp Online (includes software) and Excel Expert Certification Online (includes software & exam). Enroll in a course for detailed lessons, live instructor support, and project-based training.
Text functions. You can use text functions to help edit your data in a way that's going to be more efficient than manual data entry. We'll take a look at four different text functions here, very simple to use.
Three of them deal with the case of the text that you're working with—casing. For instance, you may run into a situation where you need to capitalize the first letter of every word in a cell. 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.
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 United States.
That's not properly capitalized, so I need to properly capitalize it. Now, I'm going to show you the 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 P-R-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 parenthesis, so you don’t have to press Shift and 9 to type the opening 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. So, without using my mouse, I can use the left arrow key.
That selects the text without even using my mouse. Then, to complete this function, I don't have to press ENTER. I need to go over to the right so I can make sure that USPS is all in uppercase.
So, the keyboard key I'm going to use to complete this entry, and I don't have to put in the closing parenthesis, because if it's a function where there are only two parentheses necessary, I can leave it out. Excel has my back. It'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 are U-P. That's the minimum I need to type.
Then, I can press TAB, left arrow, and then press TAB again. That will capitalize USPS. Now, “Return to sender”—that’s yelling at me. I need to lower the volume. I’ll literally type in equal and then L-O-W.
Then, I'll press TAB, left arrow, and then I'll press TAB again. That makes all the text lowercase. 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 =TRIM. Then, I'll press Shift and 9 to 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. And then, that removes all the extra spaces from the cell but keeps the space that's necessary to separate New York and the zip code.
So, those are four very useful text functions that you can use when you're working with text and data entry in your Excel spreadsheet. I'll quickly go through these next few exercises. We increased 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, P-R-O-P, 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.
So, now I'm going to go to UPPER. I'm going to type in equal, U-P, TAB, left arrow, press ENTER. If I want, I can select the first cell that has the formula and the blank cells underneath and then press Ctrl+D. That is the keyboard shortcut we can use.
I could also select the cell using Shift and Down Arrow to select the entire range. When I get to the bottom, I'll press Ctrl+D. So, now I want to lower the volume for the emails. Emails are not case sensitive, so the emails will all be lowercase.
=LOWER, then press TAB, 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.
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, T-R-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—Ctrl+Shift+Down—that may select that column and stop right at the USPS field in the adjacent column.
If I press Ctrl+Shift+Down, that doesn't work because that's like a bottomless pit. It goes all the way down. So, I could press Ctrl+Shift+Down.
This is what I was hoping would happen, but that's not going to 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, Ctrl+Down Arrow, to get to the bottom of the range. Now, you're exactly where you need to be, but one cell over.
So, I'm going to 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 Ctrl+V. That will paste all the formulas in that column just using keyboard shortcuts.
Then, I'll press Esc, and my work is done. So, these are four text functions that you can use to quickly edit the casing of text in your data, as well as remove spaces.
