In Microsoft Excel, the UPPER function is generally used to convert all the letters in a string of text to block ones. In this article, you’ll get to learn how you can use this UPPER function effectively in Excel with appropriate illustrations.
The above screenshot is an overview of the article, representing a few applications of the UPPER function in Excel. You’ll learn more about the methods along with the other functions to use the UPPER function with ease in the following sections of this article.
Introduction to the UPPER Function in Excel
- Function Objective:
The UPPER function is used to convert a text string to all uppercase letters.
- Syntax:
=UPPER(text)
- Argument Explanation:
| Argument | Required/Optional | Explanation |
|---|---|---|
| text | Required | A string of text where all letters have to be converted to upper cases. |
- Return Parameter:
All upper case letters in a string of text.
How to Use UPPER Function in Excel: 4 Suitable Applications
1. Basic Use of UPPER Function in Excel
While inputting a text value in the only argument of the UPPER function, you have to use double quotes (“ “) around the text data. Otherwise, the function will not be executed and an error message box will appear. In the picture below, some texts with lower cases are lying in column B. And in column C, the outputs are obtained by using the UPPER function.
For example, the first one in cell C5 comes up with the formula:
=UPPER("washington")After pressing Enter, you’ll find all the letters or alphabets in upper cases at once.
2. Use of UPPER Function with Cell Reference
The use of cell reference is much easier than typing a text manually in the text argument of the UPPER function.
For example, cell B5 in the following table contains a text with lower cases. So, in the output cell C5, the required formula by using the cell reference will be:
=UPPER(B5)You don’t have to use double quotes around the cell reference here.
3. Using Upper Function for Data Validation in Excel
Let’s think of a scenario with Excel entries where data validation is required. Suppose, in the following picture, you have to type your first and last names in cells C4 and C5. The names must be in upper cases or block letters. But if you input a lower case letter or a numeric value, an error message will appear. How will you make it happen?
📌 Step 1:
➤ From the Data ribbon, select the Data Validation command from the Data Tools drop-down.
A dialogue box will open up.
📌 Step 2:
➤ Select Custom from the Allow list for Validation criteria.
➤ Inside the formula box, you have to type:
=AND(EXACT(C4,UPPER(C4)),ISTEXT(C4))In this formula, we’re using a logical function AND that will define if the input in cell C4 contains text data with all upper case letters.
➤ Press OK and you’re done.
📌 Step 3:
➤ Now return to your spreadsheet and use Fill Handle to assign the criteria for cell C5 too.
➤ In cell C4, type a name in lower cases and press Enter.
An error message box will appear which will inform you that the input you have provided is not valid according to the defined criteria.
➤ Press Cancel and the message box will disappear.
📌 Step 4:
➤ Now type a name in block letters in cell C4.
➤ Press Enter and the cell will store the input without displaying any error message this time.
4. Joining Texts with Partial Upper Case
In the following picture, we have now a list of random smartphone brands and their model names in two columns. The model names are in lower cases that we’ll have to make upper cases while concatenating the data alongside those two columns B and C.
In the output cell D5, the required formula should be:
=B5&" "&UPPER(C5)After pressing Enter, you’ll get the return texts with the model name in block letters or upper cases. Now you can use Fill Handle to autofill the rest of the cells in column D to extract the other outputs.
💡 Things to Keep in Mind
🔺 If you need to make only the first letter upper case, then you have to use the PROPER function as the UPPER function converts all lower cases in a text to upper ones.
🔺 UPPER function does not have any crucial impact on the numeric values. But if a number is in date or currency format then the function will return the serial number of the date and remove the currency units respectively.
🔺 While inputting a text value in the argument, please do not forget to use double quotes(“ “) around the text.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Concluding Words
I hope all of the suitable methods mentioned above to use the UPPER function will now provoke you to apply them in your Excel spreadsheets with more productivity. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.
