Function guides · Text
UPPER function
Converts text to uppercase.
Syntax
=UPPER(text)
| Argument | What it is |
|---|---|
text | The text or cell to convert. |
What UPPER does
UPPER turns every letter in a piece of text into a capital. Numbers, spaces and punctuation are left alone.
It is the quickest way to bring mixed-case entries into line, which matters when text is compared. A lookup, a COUNTIF or a duplicate check treats “elc-4417” and “ELC-4417” as the same thing, but FIND, SUBSTITUTE and EXACT do not, and a clean column avoids surprises. LOWER does the opposite, and PROPER capitalizes the first letter of each word.
Like every text function, it returns a new value and leaves the source cell alone.
The three case functions are a family. UPPER makes everything a capital letter, LOWER makes everything small, and PROPER capitalizes the first letter of each word and lowercases the rest. PROPER is the usual choice for names and titles, though it needs a hand fix for names such as McDonald and for small words like “of”.
Combining them with TRIM is the standard cleanup chain: =PROPER(TRIM(A2)) removes stray spaces and standardizes the case in one step. Do the cleaning in a new column, check the result, then paste values over the old data.
UPPER examples
1. Standardize a product code
| A | B | |
|---|---|---|
| 1 | Typed | Standard |
| 2 | elc-4417 | ELC-4417 |
Codes typed by different people arrive as "elc-4417", "Elc-4417" and "ELC-4417". UPPER makes them consistent so they can be compared or looked up.
2. Capitalize only the first letter of a code
| A | B | |
|---|---|---|
| 1 | Word | |
| 2 | hawaii | Hawaii |
UPPER combines with LEFT and MID to change part of a string. This capitalizes the first letter and leaves the rest alone.
3. Case-sensitive comparison
| A | B | C | |
|---|---|---|---|
| 1 | Code A | Code B | |
| 2 | elc-4417 | ELC-4417 | TRUE |
The equals operator ignores case, so "mei" = "MEI" is TRUE. EXACT is case sensitive, so these two codes would differ. Putting UPPER around both sides first removes the difference, and the same trick makes FIND and SUBSTITUTE ignore case.
Common UPPER mistakes
- Expecting it to change the original. UPPER returns a new value. The original cell stays as typed until you paste values over it.
- Using UPPER on numbers. A number returns as text. UPPER(123) is the text "123", which then fails numeric comparisons.
- Assuming = is case sensitive. A = comparison ignores case. EXACT is the case-sensitive version.
- Using UPPER for names. "MCDONALD" is wrong where PROPER gives "Mcdonald", which is also wrong. Names with internal capitals need hand fixes.
UPPER on Excel job tests
Cleaning prompts like "standardize the state abbreviations", "convert the email addresses to lowercase" or "format the names in proper case" call for UPPER, LOWER or PROPER.
Lookups and the = comparison ignore capitals, so case conversion matters for how the text looks and for EXACT, not for whether a match is found. If the cleaned text feeds a lookup, convert both the lookup value and the key column the same way, mainly so stray spaces and mixed spellings get cleaned up together. If the task says to keep the originals, put the result in a new column.
Try all three conversions on one sample cell before filling down. PROPER capitalizes after any non-letter, which fixes "o'brien" and spoils "3rd", turning it into "3Rd".
Related functions
UPPER questions
- What does UPPER do in Excel?
- It converts every letter in the text to a capital letter.
- How do I capitalize only the first letter?
- Use PROPER for each word, or UPPER(LEFT(A2,1))&LOWER(MID(A2,2,100)) for the whole cell.
- How do I make the conversion permanent?
- Copy the formula cells and use Paste Special > Values over the original column.
- Does UPPER change numbers?
- It returns them as text. Use VALUE to convert back.
Practice UPPER
The lesson UPPER, LOWER and PROPER: fix the case (module 9 of Everyday Excel) has checked UPPER exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.
Try a 5-item Excel test sample