The PROPER, UPPER and LOWER functions in Excel
Three functions fix capitalization: PROPER ("Maya Cohen"), UPPER ("SH-4417-BLU") and LOWER ("omar@shopmail.com"). Use them to make names, codes and addresses consistent before you sort, compare or look them up.
Syntax
=PROPER(text)
| Argument | What it means |
|---|---|
| text | The text or cell to change. UPPER and LOWER take the same single argument. |
Examples
1. Names in title case
| A | B | |
|---|---|---|
| 1 | As typed | Fixed |
| 2 | nora BERG | Nora Berg |
First letter of each word capital, the rest small.
2. Codes in capitals: UPPER
| A | B | |
|---|---|---|
| 1 | As typed | Fixed |
| 2 | sh-4417-blu | SH-4417-BLU |
Digits and dashes stay as they are.
3. E-mail addresses in small letters: LOWER
| A | B | |
|---|---|---|
| 1 | As typed | Fixed |
| 2 | Omar.Haddad@ShopMail.com | omar.haddad@shopmail.com |
E-mail addresses are compared without case anyway, but a consistent list is easier to scan and deduplicate.
Common mistakes
PROPER on names like McDonald or van der Berg. It gives "Mcdonald" and "Van Der Berg".
Fix: Fix those few by hand after applying PROPER to the rest.
Expecting the original cell to change. These functions return a new value in another cell.
Fix: Copy the result and use Paste Special › Values over the original.
Frequently asked questions
Is there a function for sentence case (only the first letter capital)?
Not directly. =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,1000)) does it.
Related functions
- TRIMTRIM removes extra spaces from text: at the start, at the end, and doubles between words.
- SUBSTITUTESUBSTITUTE replaces specific text inside a cell with other text, everywhere or only one occurrence.
- CONCATENATECONCATENATE, CONCAT and the & operator join text from several cells into one, like first and last name.