If you need to change the text in a worksheet to uppercase, lowercase, or just return it to proper case, there is a function that you can use to write a formula and Excel will do the work. This lesson will discuss the upper, lower, and proper functions in Excel.
Suppose you just received a list of clients. You would like to use Word's mail merge function to create a mailing list of labels. However, the client information has some text in all caps, some in all lower case, and some is in proper case. You get ready to start re-typing the information and then remember that Excel might have a function that will do the work for you.
Well, Excel has three functions that can be used to change the case of text. The first one is the upper function, and it converts all lower case letters to upper case. The second is the lower function, which removes capital letters. And then, the proper function, which makes the first letter of each word capitalized and leaves the other letters lower case.
This lesson will demonstrate how to use each text function in a formula.
The Upper Function
Let's look at an example formula.
The above formula would convert all text in A1 to upper case. For instance, assume that you have song titles in column A. You would like all the titles to be in upper case. We will put the upper case titles in column C.
Select the cell where you will enter the formula (in our example, this would be C1). Then, type the equals sign ''(=)'' and then type ''UPPER''. Enter the cell reference (in our example, this would be A1) and then hit ''Enter'' and there you go. All letters are now capitalized.
The Lower Function
Now, the lower function works identically. Let's look at an example formula.
The steps are the same here except you type ''LOWER'' rather than ''UPPER''. It's important to understand that the changed text will be entered into the same cell you add the formula. This means that you can only enter 1 cell reference.
For example, the formula =LOWER(A2,B2) would not work and return an error. Excel will even tell you that ''There are too many arguments for this function.'' If you have two columns of data, such as the song title and the artist, you would need to write two formulas. You could enter one formula in column C and the other in column D. For example,
- =LOWER(A2) would be column C
- =LOWER(B2) would be column D
The Proper Function
Alright, now let's move on to the proper function. Again, using the proper function in a formula works the same as the upper and lower. However, with the proper function, the text is changed to mixed case. The first character of each word will convert to a capital letter, while the remaining letters will be converted to lower.
For example, in cell A3, we have the title 'count ON ME'. If we add the formula =PROPER(A3) to cell C3, the result would be ''Count On Me'', with a capital 'C', a capital 'O', and a capital 'M'; all the other letters would be lower case. Pretty slick!
Excel's three text functions are really very simple to use. Just remember the upper function converts text to upper case, the lower function converts all text to lower case, and the proper function capitalizes the first letter of the word, while changing the remaining letters to lower case.
And one final reminder: you can only have 1 cell reference in your formula. So give it a try!
After you've completed this lesson, you should have the ability to:
- Recall the purpose of Excel's upper, lower, and proper functions
- Explain how to use these functions in Excel