FormuladrillPROPER, UPPER, LOWER
UPPER(text) converts text to all capitals, LOWER(text) to all lowercase, and PROPER(text) capitalizes the first letter of every word and lowercases the rest. Combine with TRIM to clean a column of names: =PROPER(TRIM(A2)).
Syntax
PROPER(text) UPPER(text) LOWER(text)| Part | What it is |
|---|---|
text | The cell or string to convert. |
Examples
=PROPER("jane DOE")Result: Jane Doe
=UPPER("ab-12")Result: AB-12
Numbers and punctuation are left as they are.
=LOWER(A2)Result: jane.doe@example.com when A2 is Jane.Doe@Example.com
Standard for emails before matching or removing duplicates.
=PROPER(TRIM(A2))Result: Clean, consistently capitalized names from messy input
Common mistakes
- PROPER capitalizes after every non-letter: mcdonald becomes Mcdonald, o'neil becomes O'Neil, and 2nd becomes 2Nd. Review names with apostrophes and prefixes by hand.
- The original column is unchanged: these functions return a new value. Paste the results as values over the source to replace it.
- Case does not matter for =, VLOOKUP or COUNTIF, so changing case does not fix a mismatch; look for spaces instead.
Practice PROPER, UPPER, LOWER
These drills check your answer on live data, free, no account needed.
- Clean up namesThe names were typed carelessly. Remove extra spaces and give each word a capital first letter. Fill B2 and B3.
Questions
How do I capitalize the first letter of each word in Excel?
=PROPER(A2). For only the first letter of the cell use =UPPER(LEFT(A2, 1)) & LOWER(MID(A2, 2, LEN(A2))).
How do I change text to uppercase without a formula?
Excel has no shortcut key for it. Use =UPPER(A2) in a helper column, copy, and Paste Special as values, or use Flash Fill (Ctrl+E) after typing one example.
Related
- TRIMHow the Excel TRIM function removes leading, trailing and repeated spaces, why lookups fail without it, and how to remove the non-breaking spaces it leaves.
- SUBSTITUTEHow the Excel SUBSTITUTE function replaces one piece of text with another, with examples for removing dashes, swapping the nth occurrence and counting commas.
- CONCAT and &How to join text in Excel with CONCAT, CONCATENATE and the & operator, with examples for full names, email addresses, numbered IDs and a formatted date.
- LEN, LEFT, RIGHT, MIDHow to pull characters from the start, end or middle of a cell with LEFT, RIGHT and MID, measure length with LEN, and use FIND to split text at a character.