In Excel. there are text functions that allow you to quickly change the case of the text (to lower case, upper case, or proper case).
Below is an example of each type of case:
Excel UPPER Function – Overview
UPPER function is one of the many text function in Excel.
What does it do?
It takes a string as the input and converts all the lower case characters in that text string to upper case.
When to Use it?
Use it when you have a text string and you want to convert it all characters into upper case. This can often be the case when you get the data from an external source and you want to clean it and make it consistent.
One example of this could be when you have Product Ids in inconsistent format and you want to make it all in upper case.
UPPER Function Syntax
- text: This is the text string in which you want to convert lower case characters to upper case.
Examples of Using Excel Upper Function
Here are some practical examples to show you how to use the UPPER function in an Excel worksheet.
Example 1 – Convert text in all Cells into Uppercase
Suppose you have a dataset as shown below where you want to make it consistent and make all the cells text in uppercase.
The below formula would convert all the lowercase characters into the upper case:
Example 2 – Combine Cells While Keeping one Part in Upper Case
Suppose you have the address data as shown below and you want to combine this data.
Since the state code is in lower case, you can use the combination of TextJoin and Upper formula to combine and get the address with state codes in upper case.
Below is the formula that would do this:
If you’re using a version where TextJoin function is not available, you can use the below formula (which uses the CONCATENATE function):
=CONCATENATE(A2,", ",B2,", ",UPPER(C2))
Some useful things to know about the UPPER Function:
- Numbers, special characters, and punctuations are not changed by the Upper function.
- The UPPER function only affects the lowercase characters of the text string. Any character other than the lowercase text characters is left unchanged.
- If you use a null character (or a reference to an empty cell), it will return a null character.
Other Useful Excel Functions:
- Excel FIND Function – The FIND function finds a text string within another text string and returns its position
- Excel Lower Function. – The Lower function converts a given text string from upper case to lower case. Numbers, punctuations, and special characters are not changed.
- Excel PROPER Function. – The PROPER function converts a given text string to the proper case (where the first letter of each word is capitalized). Numbers, punctuations, and special characters are not changed
- Excel TRIM Function – The TRIM function removes leading, trailing, and double spaces from a text string.
- Excel LEN Function: It counts the total number of characters in a cell. It’s commonly used to get the length of a text string.
Similar Function in VBA:
- VBA UCase Function – It converts a text string to the upper case in VBA.