Spreadsheet data frequently suffers from formatting inconsistencies, especially when imported from external forms or typed manually by multiple people. You might have a list of names where some are entirely lowercase (john smith), some are entirely uppercase (JOHN SMITH), and some are a chaotic mix (jOhN sMiTh).
Manually retyping these names to look professional is a massive waste of time. Google Sheets provides three dedicated text formulas to instantly standardize capitalization across your entire dataset: PROPER, UPPER, and LOWER.
In this guide, you will learn how to use these three simple functions to clean and format text strings.
1. The PROPER Function
The PROPER function is the most commonly used formatting tool. It forces the very first letter of every single word in a cell to be capitalized, while forcing all subsequent letters in that word to be lowercase. It is perfect for formatting names, cities, and job titles.
=PROPER(text)Example:
If cell A2 contains the text jOhN sMiTh.
You type: =PROPER(A2)
The output will perfectly format to: John Smith
2. The UPPER Function
The UPPER function does exactly what it sounds like: it forces every single alphabetical character in the referenced cell to become an uppercase capital letter. It completely ignores numbers and punctuation.
This is highly useful when you need to standardize postal codes, state abbreviations, or product SKU codes to match a master database format.
=UPPER(text)Example:
If cell A2 contains the text sku-abc-123.
You type: =UPPER(A2)
The output will transform to: SKU-ABC-123
3. The LOWER Function
The LOWER function is the inverse of UPPER. It forces every single alphabetical character in the cell to become lowercase. Like UPPER, it ignores numbers and punctuation.
This function is critical when preparing data for systems that are case-sensitive, such as generating standardized email addresses or formatting URL slugs where capital letters are prohibited.
=LOWER(text)Example:
If cell A2 contains the text [email protected].
You type: =LOWER(A2)
The output will sanitize to: [email protected]
Workflow Tip: Hardcoding the Results
When you use these formulas, the new, cleanly formatted text is generated in a new column. You cannot delete the original messy column (Column A), or the formulas in Column B will break and display an error.
To finalize your data cleanup:
- Highlight all the newly generated text in your formula column.
- Copy the cells (Ctrl+C or Cmd+C).
- Right-click on the very first cell of the original, messy column.
- Select Paste special > Values only (or press Ctrl+Shift+V).
This overwrites the messy data with the clean text. You can now safely delete the temporary column containing the PROPER/UPPER/LOWER formulas, leaving you with a perfectly sanitized database.