Excel UPPER Function
UPPER Function
The UPPER function is a premade function in Excel, which changes all the letters in a text to uppercase (capital letters).
Digits, spaces and symbols are not changed.
It is typed =UPPER and has one part:
=UPPER(text)
Text: The text to change. This is usually a cell, like A2.
How to use the UPPER function:
- Select a cell (
B2) - Type
=UPPER - Double click the UPPER command
- Select the cell with the text (
A2) - Hit enter
Let's have a look at an example!
The Poke Mart item codes should be in capital letters, like PKB-0200-BALL. But some codes were typed in lowercase or mixed case. Copy them and paste them into cell A1 to follow along:
Example data
A B 1 Typed Code Code 2 pkb-0200-ball 3 Grb-0600-Ball 4 ULB-1200-ball 5 pot-0300-meds 6 Spt-0700-Meds 7 HPT-1200-MEDS 8 rev-1500-MEDS 9 ant-0100-stat
Use the UPPER function to fix the codes, in column B:
| A | B | |
|---|---|---|
| 1 | Typed Code | Code |
| 2 | pkb-0200-ball | PKB-0200-BALL |
| 3 | Grb-0600-Ball | GRB-0600-BALL |
| 4 | ULB-1200-ball | ULB-1200-BALL |
| 5 | pot-0300-meds | POT-0300-MEDS |
| 6 | Spt-0700-Meds | SPT-0700-MEDS |
| 7 | HPT-1200-MEDS | HPT-1200-MEDS |
| 8 | rev-1500-MEDS | REV-1500-MEDS |
| 9 | ant-0100-stat | ANT-0100-STAT |
=UPPER(A2)
The function is repeated with the filling function for each row, down to B9.
All the letters are now uppercase. The digits and the dashes stay the same. HPT-1200-MEDS was already in uppercase, so it does not change.
Note: In Excel for Microsoft 365 and Excel 2021 or later, you can also type =UPPER(A2:A9) in B2. The results then spill down to B9 by themselves.
The codes in column B are formulas. To replace the typed codes with the fixed ones, copy B2:B9 and paste them into A2:A9 as values. Right click A2 and choose Values under Paste Options. Then you can delete column B.
Check if a Text is Uppercase
An equal sign = does not see the difference between uppercase and lowercase. =A2=UPPER(A2) returns TRUE, even when A2 is all lowercase.
Use the EXACT function instead. EXACT compares two texts, and returns TRUE only if they are exactly the same, including uppercase and lowercase letters.
Type the labels Is Uppercase in C1 and Equal Sign in D1. Then fill both formulas down to row 9:
| A | C | D | |
|---|---|---|---|
| 1 | Typed Code | Is Uppercase | Equal Sign |
| 2 | pkb-0200-ball | FALSE | TRUE |
| 3 | Grb-0600-Ball | FALSE | TRUE |
| 4 | ULB-1200-ball | FALSE | TRUE |
| 5 | pot-0300-meds | FALSE | TRUE |
| 6 | Spt-0700-Meds | FALSE | TRUE |
| 7 | HPT-1200-MEDS | TRUE | TRUE |
| 8 | rev-1500-MEDS | FALSE | TRUE |
| 9 | ant-0100-stat | FALSE | TRUE |
The formula in C2:
=EXACT(A2, UPPER(A2))
The formula in D2:
=A2=UPPER(A2)
Only HPT-1200-MEDS was typed in uppercase, so EXACT returns TRUE only for that code. The equal sign returns TRUE for all of them.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Make Codes From Names
Combine UPPER with LEFT to make short codes from names. LEFT takes the first characters, and UPPER changes them to capital letters.
Type these Pokemon names in A2:A6 of a new sheet:
| A | B | |
|---|---|---|
| 1 | Name | Code |
| 2 | Bulbasaur | BUL |
| 3 | Charmander | CHA |
| 4 | Squirtle | SQU |
| 5 | Pikachu | PIK |
| 6 | Eevee | EEV |
=UPPER(LEFT(A2, 3))
LEFT takes the first 3 characters of each name, and UPPER makes them uppercase. Bulbasaur becomes BUL, and Pikachu becomes PIK.
Related Functions
UPPER is one of three functions that change the letter case, together with LOWER and PROPER. To remove extra spaces as well, use TRIM.
