How to Change Text Case in Google Sheets
=UPPER(text), =LOWER(text), =PROPER(text).
What these functions do
Imported data often arrives in inconsistent case: names in capitals, emails with mixed case. These three functions return a copy of the text in the case you want. The original cell is not changed.
Syntax
=UPPER(text)
=LOWER(text)
=PROPER(text_to_capitalize)
Each takes a single argument: a quoted string or a cell reference. According to Google's help pages, UPPER converts to uppercase, LOWER to lowercase, and PROPER uppercases the first letter of each word and lowercases the rest.
A step-by-step example
Here are two messy values in cells A1:A3.
| A | |
|---|---|
| 1 | Text |
| 2 | ann LEE |
| 3 | Admin@Example.COM |
To fix the name in A2:
=PROPER(A2)
This returns Ann Lee. PROPER capitalizes the first letter of every word and lowercases everything else, so the stray capitals in LEE are fixed as well.
To normalize the email in A3, use LOWER:
=LOWER(A3)
This returns admin@example.com. And =UPPER(A2) would return ANN LEE.
The limits of PROPER
PROPER lowercases every letter that is not at the start of a word. Google's page gives the example that PROPER("mcLeod") returns Mcleod, not McLeod. It also capitalizes every word, so it is not suited to sentences or paragraphs. Review names with unusual capitalization by hand.
Common mistakes
- Expecting the original cell to change. These functions return a new value. Copy and paste as values to replace the source.
- Using PROPER on whole sentences. Every word gets a capital letter.
- Comparing text with different case. To compare reliably, wrap both sides in LOWER.
Try it yourself
Use the text above (A2 and A3). Type your formula into the box, starting with =, then press Check.
Exercise 1: Fix the name
Write a formula that turns the text in A2 into proper name case (Ann Lee).
Exercise 2: Lowercase the email
Write a formula that turns the email in A3 into all lowercase.
What to learn next
- TRIM: remove extra spaces from text.
- CONCATENATE: join text from several cells.
- SUBSTITUTE: replace text inside a string.
Syntax and rules in this guide were checked against Google's UPPER, LOWER and PROPER help page. See all guides.