SheetsdrillPROPER, UPPER and LOWER
UPPER(text) converts text to capitals, LOWER(text) to lowercase, and PROPER(text) capitalizes the first letter of every word and lowercases the rest. Use them to standardize names, emails and codes before comparing or deduplicating.
Syntax
=PROPER(text), =UPPER(text), =LOWER(text)| Part | What it is |
|---|---|
text | The cell or string to convert. |
Examples
=PROPER("ana LOPEZ")Result: Ana Lopez
=LOWER("Ana.Lopez@Example.com")Result: ana.lopez@example.com
=UPPER(LEFT(A2, 3))&"-"&B2Result: A code such as LOP-2041 from a name and number
Common mistakes
- PROPER capitalizes after every non-letter, so o'neil becomes O'Neil (fine) but mcdonald stays Mcdonald and ipad becomes Ipad; fix those by hand or with SUBSTITUTE.
- The functions return a new value; they do not change the original cell, so paste as values if you want to replace the source.
- Comparisons in Sheets are usually case insensitive already (=, VLOOKUP, COUNTIF), so LOWER is only needed for EXACT, UNIQUE and regex.
Practice it
The drills on Sheetsdrill check your answer on live data, free, no account needed.
Start practicingQuestions
Is there a menu option to change case?
No built-in menu item; use a helper column with PROPER, UPPER or LOWER and paste the values back.
How do I capitalize only the first letter of a sentence?
=UPPER(LEFT(A2))&LOWER(MID(A2, 2, LEN(A2))).
Related
- TRIMHow TRIM in Google Sheets removes leading, trailing and repeated spaces, how to clean a whole column, and how to clear non-breaking spaces and line breaks.
- LEFT, RIGHT and MIDHow LEFT, RIGHT and MID in Google Sheets extract characters from the start, end or middle of a cell, with FIND for variable positions, and common errors.
- UNIQUEHow the UNIQUE function in Google Sheets returns a list of distinct values or rows, how to sort and count them, and the errors people run into.
- SUBSTITUTEHow SUBSTITUTE in Google Sheets replaces one piece of text with another, removes characters, targets one occurrence, and how it differs from REGEXREPLACE.