UPPER, LOWER and PROPER in Excel: how to use it
UPPER puts text in capitals, LOWER in lower case, and PROPER gives every word a capital letter at the start.
Try the first exercises for freeIn the lesson UPPER, LOWER and PROPER
What do UPPER, LOWER and PROPER do?
UPPER puts a text entirely in capitals, LOWER entirely in lower case. PROPER gives every word a capital letter at the start and puts the rest in lower case.
You use them to tidy up a messy list: names that were sometimes typed in capitals and sometimes in lower case, or codes that have to look the same everywhere. The result is a new text in another cell; the original cell doesn't change.
When do you use UPPER, LOWER and PROPER?
- Codes and abbreviations always in capitals: =UPPER(A2).
- Email addresses in lower case: =LOWER(B2).
- Names neatly with a capital at the start: =PROPER(A2).
- Cleaning up a messy name in one go: =PROPER(TRIM(A2)).
Syntax
The syntax is the fixed pattern of a function. The words in the brackets are placeholders: that's where you fill something in yourself, such as a cell, a number or a piece of text. Everything else you type exactly as it is.
The whole text in capitals.
Filled in: =UPPER(A2)
This is how to read the formula, part by part:
| Part | What it means | In the example |
|---|---|---|
= | Every formula starts with an equals sign. Without =, Excel treats what you type as ordinary text. | = |
UPPER | The name of the function: that's how Excel knows what to do. You can type it in lower case, Excel puts it in capitals itself. | UPPER |
( ) | Straight after the name come the brackets. Inside them is what the function needs to work: the arguments. | ( ) |
text | The cell with the text you convert. | A2 |
UPPER, LOWER and PROPER step by step
C1 contains =PROPER(A1). Every word starts with a capital, and the rest goes lower case. Change the numbers or the formula and see what happens.
This is how to build the formula yourself:
- Choose the function that fits: for a name, that's PROPER. In C1, type the function with a bracket.
=PROPER( - Click the cell with the name, A1.
=PROPER(A1 - Close the bracket and press Enter: every word now starts with a capital letter, and the rest is lower case.
=PROPER(A1)
More examples
A code in capitals
C1 contains =UPPER(A2). All the letters become capitals. Digits and hyphens stay as they are.
An email address in lower case
C1 contains =LOWER(A2). An email address works with capitals too, but in lower case your list is tidier and you spot duplicate addresses more easily.
Only the first letter of a sentence as a capital
C1 contains =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,1000)). PROPER would give every word a capital. This way, only the first letter gets one.
Which one do you choose?
UPPER for codes and abbreviations, such as =UPPER(A1). LOWER for email addresses. PROPER for names and towns.
Where it often goes wrong
- PROPER turns McDonald into Mcdonald. You'll have to fix names like that yourself afterwards.
- The result is new text: the original cell doesn't change. Paste the result as values if you want to replace the original.
Practise this in the lesson UPPER, LOWER and PROPER. The first exercises are free.
Frequently asked questions
How do I make only the first letter of a sentence a capital?
With =UPPER(LEFT(A1,1))&LOWER(MID(A1,2,1000)). PROPER would give every word a capital.
Do capital letters matter when looking things up?
Not for VLOOKUP, COUNTIF or IF: Leeds and LEEDS are the same there. SUBSTITUTE and FIND do take capitals into account.