The UPPER, LOWER, PROPER guide
=PROPER(A2) gives every word a capital first letter and makes the rest small. =LOWER(B2) makes every letter small and =UPPER(C2) makes every letter a capital. Each fits a different kind of data.
Which one for what
| Function | Result | Use it for |
|---|---|---|
PROPER | Dev Patel | names, cities, titles |
LOWER | [email protected] | email addresses, usernames |
UPPER | LS6 2AB | postcodes, codes, country codes |
Excel compares text without looking at capitals, so "DEV PATEL" and "Dev Patel" match in a lookup either way. The capitals matter for what people read: letters, labels, exports to other systems.
How PROPER decides
PROPER capitalises the first letter of the text and every letter that follows something that isn't a letter: a space, a hyphen, an apostrophe, a dot, a digit. Everything else becomes small. That gives Anna-Lena and O'Neill, which is right, and a few results that aren't:
- McDonald becomes Mcdonald, and van der Berg becomes Van Der Berg.
- Jim's becomes Jim'S.
- 3rd becomes 3Rd.
- [email protected] becomes [email protected], which is why emails get LOWER.
Run PROPER on the column, then look down it for names like these and fix them by hand.
Replacing the original column
The formulas go in a new column, because a cell can't hold both the text and a formula that reads it. When the clean column is right, copy it and paste it as values over the old one, then delete the helper column.
Common mistakes
- PROPER on email addresses. Each part after a dot or an @ gets a capital.
- UPPER for names. MARGARET OKAFOR reads as shouting in a letter.
- PROPER on postcodes. The letters after the digits come out small: Ls6 2Ab.
Names joined into a greeting: join text together.
FAQ
- How do I capitalise the first letter of each word in Excel?
- Use PROPER: =PROPER(A2) turns "dev patel" and "DEV PATEL" into "Dev Patel".
- How do I change text to all capitals in Excel?
- Use UPPER: =UPPER(A2). LOWER does the opposite.
- Why does PROPER turn McDonald into Mcdonald?
- PROPER only capitalises a letter at the start of a word or after a character that isn't a letter. The D in McDonald follows a letter, so it becomes small. Fix those names by hand.
- Is there a sentence case function in Excel?
- No. =UPPER(LEFT(A2,1))&LOWER(MID(A2,2,1000)) gives a capital first letter and small letters after it.



































































