How to use the UPPER function
What is the UPPER function?
The UPPER function converts letters in a value to upper case letters.
What are upper case letters?
Upper case letters refer to the large form of the letters of the alphabet, they are the opposite of lower case letters.
The upper case letters are:
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
Table of Contents
- UPPER Function Syntax
- UPPER Function Arguments
- UPPER Function example
- UPPER Function not working
- How to change lower letter to upper letter for the position of a given character
- How to change lower letter to upper letter for a given character
- How to change all lower letters to upper letters for a given character
- Get Excel *.xlsx file
1. UPPER Function Syntax
UPPER(text)
2. UPPER Function Arguments
| text | Value to convert. Required. |
3. UPPER Function example
Formula in cell C3:
3. UPPER Function not working
- Check your spelling.
- Check the number of arguments you use, the UPPER function accepts only one argument.
4. How to change lower letter to upper letter for the position of a given character
The image above demonstrates a formula that replaces the third letter with an upper case letter.
Formula in cell C3:
4.1 Explaining formula
Step 1 - Extract letter based on position
The MID function returns a substring from a string based on the starting position and the number of characters you want to extract.
MID(text, start_num, num_chars)
MID(B3, 3, 1)
becomes
MID("oranges", 3, 1)
and returns "a".
Step 2 - Convert to upper letter
The UPPER function converts a string to capital letters.
UPPER(MID(B3, 3, 1))
becomes
UPPER("a")
and returns "A".
Step 3 - Replace character based on position to an upper letter
The REPLACE function substitutes a string based on character position and length.
REPLACE(old_text, start_num, num_chars, new_text)
REPLACE(B3, 3, 1, UPPER(MID(B3, 3, 1)))
becomes
REPLACE(B3, 3, 1, "A")
becomes
REPLACE("oranges", 3, 1, "A")
and returns "orAnges".
5. How to change a given lower letter to an upper letter
The formula in cell C3 demonstrated in the image above converts the second lower case "p" from the right with an upper case "P".
Formula in cell C3:
5.1 Explaining formula
The SUBSTITUTE function substitutes a string with another string.
SUBSTITUTE(text, old_text, new_text, [instance_num])
SUBSTITUTE(B3, "p", "P", 2)
The first argument is a cell reference to cell B3, the second argument is the string to be replaced.
The third argument is what will be there after the substitution and the fourth argument is a number representing the instance.
6. How to change all lower letters to upper letters for a given character
The picture above shows a formula in cell C3 that converts all lower case "p" to an upper case "P" in cell B3.
Formula in cell C3:
The SUBSTITUTE function substitutes a string with another string.
SUBSTITUTE(text, old_text, new_text, [instance_num])
If the instance_num argument is omitted all instances are replaced.
Useful links
UPPER function - Microsoft Excel
Change the case of text
'UPPER' function examples
Table of Contents Count a specific text string in a cell (case sensitive) Count text string in a range (case […]
This article demonstrates an array formula that counts how many times multiple text strings exist in a cell range. The […]
In this post I will describe a basic user defined function with better search functionality than the array formula in […]
Functions in 'Text' category
The UPPER function function is one of 30 functions in the 'Text' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form