Changing Case With UPPER, LOWER, PROPER

Imported data is often inconsistent: JOHN smith, mary JONES, aLeX lee. Mixed capitalization looks unprofessional and can break comparisons and lookups.

Three simple functions fix this instantly:

• UPPER makes everything uppercase
• LOWER makes everything lowercase
• PROPER capitalizes the first letter of each word

Each takes a single piece of text and returns a re-cased copy.

UPPER converts every letter in a string to a capital letter. Numbers and symbols are left unchanged.

The shape is =UPPER(text). If A2 holds shoe-4471, the formula returns SHOE-4471.

This is handy for product codes, country codes, and abbreviations that should always be capitals.

LOWER does the opposite: it turns every letter into lowercase.

The shape is =LOWER(text). If A2 holds Ada.LOVELACE@Coddykit.com, the formula returns ada.lovelace@coddykit.com.

Lowercasing is especially useful for email addresses and usernames, which are usually treated as all-lowercase.

PROPER capitalizes the first letter of every word and lowercases the rest. This is perfect for names and titles.

The shape is =PROPER(text). If A2 holds ADA lovelace, the formula returns Ada Lovelace.

A word is any group of letters separated by spaces or punctuation.

Suppose column A holds messy names like jOHN SMITH and mary jones.

Apply PROPER to turn each into clean title case: John Smith and Mary Jones.

You would put this in column B, then copy it down the whole column to clean every name at once.

PROPER follows a simple rule, so it can mis-capitalize special cases:

• Names like McDonald become Mcdonald
• Abbreviations like USA become Usa
• Words after an apostrophe, like O'Brien, become O'Brien (the B is capitalized, which is sometimes wrong)

For these you may need to fix the result manually or combine functions. Always spot-check PROPER output.

You can nest these functions inside the joining formulas from earlier lessons.

Suppose A2 is a sloppy first name and B2 a sloppy last name. Clean and join them in one formula:

The result is a properly cased full name, no matter how the source was typed.

A classic real task: generate a standard email from a name. Emails should be all lowercase even if the names are not.

Wrap the joined name parts in LOWER so the whole address comes out clean.

If A2 is Ada and B2 is Lovelace, the result is ada.lovelace@coddykit.com.

Spreadsheet text comparisons often ignore case, but not always, and lookups can be picky. Forcing both sides to the same case makes comparisons dependable.

For example, to check whether two cells match regardless of capitalization, compare their uppercase versions.

This returns TRUE for Apple versus APPLE.

Good news: UPPER, LOWER, and PROPER work identically in both Microsoft Excel and Google Sheets. The syntax and behavior match exactly.

That makes them safe building blocks when you share workbooks across platforms.

Remember the limits of PROPER on special names apply in both tools too.

If your source text is in all caps, PROPER still works, but some people first apply LOWER for predictable results, then PROPER.

The formula =PROPER(LOWER(A2)) guarantees that only the intended first letters are capitalized, even from messy ALL-CAPS input.

It is an optional habit that makes the transformation explicit and easy to read.

Test your grasp of the case functions.

You learned three case functions:

• UPPER(text) makes everything uppercase.
• LOWER(text) makes everything lowercase.
• PROPER(text) capitalizes the first letter of each word.

Watch out: PROPER mishandles names like McDonald and abbreviations like USA. You can nest these inside joins and use UPPER on both sides for case-insensitive comparisons.

Next, you will swap out characters and words with SUBSTITUTE.