UPPER, LOWER and PROPER in Google Sheets: change text case
UPPER turns a text into capitals, LOWER into small letters, and PROPER capitalizes the first letter of every word. Google Sheets has no menu command for changing case, so this guide shows the formulas, how to turn their results into plain values, how to capitalize only the first letter of a sentence, and where PROPER gets names and abbreviations wrong.
Three functions change the case of a text in Google Sheets:
| Function | “john SMITH” becomes |
|---|---|
=UPPER(A2) |
JOHN SMITH |
=LOWER(A2) |
john smith |
=PROPER(A2) |
John Smith |
Each takes one argument, the text or a cell that holds it, and leaves digits and punctuation as they are.
Change case without a formula
Google Sheets has no menu command for changing case. The Format > Text menu holds only bold, italic, underline and strikethrough; the Capitalization submenu that Google Docs has is not there. The way to get the same result is a formula followed by a paste of its values:
- In an empty column, enter the formula, for example
=UPPER(A2), and fill it down. - Select the results and copy them.
- Select the original cells and choose Edit > Paste special > Values only (Ctrl+Shift+V, or Cmd+Shift+V on a Mac).
- Delete the helper column.
The original cells now hold the converted text and no formula.
UPPER
=UPPER(text)
=UPPER(B3)
UPPER is the usual way to make codes comparable: country codes, SKUs, coupon codes. Accented letters are converted as well, and the German ß becomes SS.
OWOX Data Marts
See your first report built in real time. 15 minutes.
- Connect your data warehouse
- Pick your metrics
- Get a live Google Sheets report
In the time it takes to write a ticket. Then imagine never writing that ticket again.
Book a DemoWe'll use your actual use case
LOWER
=LOWER(text)
=LOWER(B3)
The classic use is email addresses, which arrive in every mix of cases from forms and exports:
=LOWER(C3)
Lowercasing matters before removing duplicates and before a lookup against a case-sensitive system. Inside Sheets, VLOOKUP, COUNTIF and UNIQUE treat case differently: VLOOKUP and COUNTIF ignore it, UNIQUE does not, so “Anna@x.com” and “anna@x.com” stay two rows in a UNIQUE list until they are lowercased.
PROPER
=PROPER(text_to_capitalize)
=PROPER(B3)
PROPER capitalizes the first letter of every word and lowercases the rest. A “word” for PROPER starts after anything that is not a letter, which gives these results:
| Text | PROPER returns | What happened |
|---|---|---|
| o’connor | O’Connor | The letter after the apostrophe is capitalized |
| john’s | John’S | The same rule, where it is not wanted |
| mcdonald-smith | Mcdonald-Smith | Capital after the hyphen, none inside “Mcdonald” |
| nasa | Nasa | Abbreviations lose their capitals |
| iPhone | Iphone | Mixed-case names are flattened |
| 2nd | 2Nd | The letter after a digit is capitalized |
| Capital after the hyphen |
PROPER is right for most first and last names and wrong for possessives, abbreviations and brand names. After applying it, fix the exceptions. For possessives, a regular expression that matches “’S” only at the end of a word leaves names such as O’Sullivan alone: =REGEXREPLACE(PROPER(A2), "'S\b", "'s"). For abbreviations, use SUBSTITUTE: =SUBSTITUTE(PROPER(A2), "Nasa", "NASA").
A whole column at once
With ARRAYFORMULA, one formula in the first row converts the column:
=ARRAYFORMULA(UPPER(C3:C10))
=ARRAYFORMULA(PROPER(B3:B10))
See array formulas.
Capitalize only the first letter of a sentence
Capitalize only the first letter of a sentence
PROPER capitalizes every word. For sentence case, take the first letter in capitals and the rest in small letters:
=UPPER(LEFT(C3,1))&LOWER(RIGHT(C3,LEN(C3)-1))
- UPPER(LEFT(C3,1)): the first character, capitalized.
- LOWER(RIGHT(C3,LEN(C3)-1)): everything after it, in small letters.
Names and abbreviations inside the sentence are lowercased too (“call john at nasa”), so read the results. See LEFT, RIGHT and MID.
With other functions
TRIM: clean the spaces first
=PROPER(TRIM(C3))
TRIM removes the spaces at both ends and the double spaces inside, PROPER fixes the case.
CONCATENATE: join, then convert
=PROPER(CONCATENATE(B3, " ", C3))
=UPPER(CONCATENATE(B3, " ", C3))
The ampersand does the same: =UPPER(B3 & " " & C3). See CONCATENATE and the ampersand.
IF: convert only some rows
=IF(C3="VIP", UPPER(B3), B3)
EXACT: find the cells in the wrong case
EXACT compares two texts with their case. A text equals its own uppercase version only if it is already in capitals:
=EXACT(A2, UPPER(A2))
The formula returns TRUE for “ABC-01” and FALSE for “Abc-01”. Used as a custom formula in conditional formatting with NOT around it, =NOT(EXACT(A2, UPPER(A2))), it highlights the codes that are not fully in capitals.
Numbers, dates and other languages
Numbers, dates and other languages
- Numbers: the functions return text.
=UPPER(123)gives the text “123”, which SUM ignores. - Dates: the date is converted as it is displayed. UPPER on a cell showing 1/5/2023 returns the text “1/5/2023”, no longer a date.
- Accented letters: é, ü and ñ are converted correctly.
- Language-specific rules: the functions follow general Unicode rules, not the rules of one language. The Turkish capital İ, for example, is not lowered to a plain i. Check the results for languages with special casing.
Errors and wrong results
⚠️ Error: The formula shows the same text as before.
✅ Solution: The text has no letters to change (digits, symbols), or it is already in that case.
⚠️ Error: PROPER breaks possessives, abbreviations or brand names.
✅ Solution: That is how PROPER defines a word; see the table above. Correct the exceptions with REGEXREPLACE or SUBSTITUTE.
⚠️ Error: The original column still has the old text.
✅ Solution: The functions return a new value and do not change their source. Paste the results over the originals as values only.
⚠️ Error: After deleting the source column, the results turn into #REF!.
✅ Solution: The formulas still point to the deleted cells. Paste them as values before removing the source.
⚠️ Error: A converted number no longer adds up.
✅ Solution: The result is text. Do not apply case functions to numeric columns, or convert back with VALUE.
Related Google Sheets functions
Related Google Sheets functions
- TRIM, CLEAN and T: Remove extra spaces and non-printing characters.
- SUBSTITUTE: Replace one text with another.
- EXACT: Compare two texts with their case.
- LEFT, RIGHT and MID: Extract a part of a text.
- CONCATENATE and the ampersand: Join texts.
- Remove duplicates: Why case matters before deduplication.
- Data validation: Stop mixed case at entry.
- REGEX functions: Match and replace by pattern.
Case differences are a source problem
Case differences are a source problem
Mixed case in a sheet usually means the same value was typed or exported in several ways: “Facebook”, “facebook”, “FACEBOOK” as three campaign sources. LOWER makes them match in this file. The next export brings the three spellings back, and every report built on that data has to repeat the fix or silently split one channel into three rows.
The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.
What changes for the person who maintains the sheet:
- Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
- The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
- No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.
The extension does not write or change the analyst’s SQL. When the spelling is normalized once in that SQL, the values arrive in one case, and UPPER, LOWER and PROPER are needed only for presentation. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.
Turn your data into decisions.
Governed data marts give you the clean foundation ML needs to actually work.
- No AI hallucinations
- Analyst-governed definitions
- Every number traces to SQL
Frequently Asked Questions
What is the purpose of the PROPER function in Google Sheets?
The PROPER function in Google Sheets capitalizes the first letter of each word while converting all other letters to lowercase. Use =PROPER(text) to format names or titles correctly. It may not work perfectly for abbreviations like "NASA" or "iPhone."
Can I use these functions in combination with Google Sheets' conditional formatting?
Yes! Use UPPER, LOWER, or PROPER with custom formulas in conditional formatting. Example: To highlight uppercase text in A1:A10, use: =EXACT(A1, UPPER(A1)). This applies formatting when the text is fully uppercase. Adjust the formula for lowercase or title case checks.
How can I remove case formatting and keep the original text unchanged?
To remove case formatting while keeping the original text, copy the converted text and use Paste values only (Ctrl + Shift + V on Windows or Cmd + Shift + V on Mac). This replaces formulas with static text, ensuring the text stays unchanged after removing the case conversion function.
Can these functions handle non-English characters?
Yes, the UPPER, LOWER, and PROPER functions in Google Sheets support non-English characters, including accented letters (e.g., é, ü, ñ). However, they may not correctly handle some languages with unique casing rules, such as Turkish. Always verify results for special characters.
Can I use UPPER, LOWER, and PROPER functions on multiple cells at once?
Yes! Apply UPPER, LOWER, or PROPER to multiple cells using an array formula. Example: =ARRAYFORMULA(UPPER(A1:A10)). This converts all text in A1:A10 to uppercase. Replace UPPER with LOWER or PROPER for lowercase or title case formatting.
How can I convert text to lowercase in Google Sheets?
Use the LOWER function in Google Sheets to convert text to lowercase. The syntax is =LOWER(text), where text can be a string or cell reference. It changes all letters to lowercase while keeping numbers and symbols unchanged.
What is the UPPER function in Google Sheets, and how does it work?
The UPPER function in Google Sheets converts text to uppercase. Use =UPPER(text) to change lowercase letters to capitals while keeping numbers and symbols unchanged.
