Unlike Microsoft Word, which has a built-in "Change Case" button on the ribbon, Microsoft Excel and Google Sheets do not have a simple one-click solution to fix messy capitalization. If you import a massive list of improperly formatted names, emails, or addresses, fixing them manually is a nightmare.
Fortunately, both Excel and Google Sheets share three very simple, built-in functions to automatically convert your text cases.
1. The UPPER Function (UPPERCASE)
If you need to make an entire string of text capital letters (useful for postal codes, product
IDs, or standardized forms), use the UPPER function.
- Formula:
=UPPER(A1) - Example: "john doe" becomes JOHN DOE
2. The LOWER Function (lowercase)
The LOWER function is incredibly useful for standardizing email addresses or
database entries where capital letters might cause formatting errors down the line.
- Formula:
=LOWER(A1) - Example: "[email protected]" becomes [email protected]
3. The PROPER Function (Title Case)
This is arguably the most frequently used text-case formula. The PROPER function
capitalizes the first letter of every word in a string and forces all subsequent
letters in the word to be lowercase. This is perfect for cleaning up lists of names or cities.
- Formula:
=PROPER(A1) - Example: "jOhN dOe" becomes John Doe
How to Replace the Messy Text with Clean Text
When you use these formulas, the result appears in a new column, leaving your messy original text behind. To replace the original data:
- Highlight the new column containing your perfectly formatted text and press Ctrl+C (Copy).
- Click on the very first cell of your original, messy column.
- Right-click and select Paste As Values (123). In Google Sheets this is Paste Special > Values only.
- You can now delete the column with the formulas!
The Faster Alternative
If you don't want to mess around with creating new columns, typing formulas, and pasting values backward, there's a much faster way.
Simply copy the entire messy column from your spreadsheet, paste it directly into TextCaseTools, click the formatting button you want (e.g., "Title Case", "Lowercase"), and copy it right back into Excel in seconds. No formulas required!