UPPER, LOWER and PROPER in Excel and Google Sheets

The three functions

Excel and Google Sheets have three text functions for case. UPPER(text) converts all letters to capitals. LOWER(text) converts all letters to lowercase. PROPER(text) capitalizes the first letter of each word and lowercases the rest. If cell A1 contains the text hello WORLD, UPPER(A1) returns HELLO WORLD, LOWER(A1) returns hello world and PROPER(A1) returns Hello World.

The functions work on text only; they leave digits and punctuation alone. They return a new text value, so the original cell stays unchanged. To keep the converted text and delete the original column, copy the formula cells and use Paste Special with the option Values, then delete the source column.

Where PROPER goes wrong

PROPER follows a simple rule: it capitalizes any letter that comes after a character that is not a letter, and lowercases all other letters. That rule has side effects. PROPER("don't") returns Don'T in Excel, because the letter after the apostrophe counts as the start of a word. PROPER("1st place") returns 1St Place for the same reason, because the letter after a digit is capitalized.

The function also does not know names. McDonald becomes Mcdonald, van der Berg becomes Van Der Berg, and acronyms such as NASA become Nasa. It does not keep small words in lowercase either, so a title like The Lord of the Rings becomes The Lord Of The Rings. For text where these things matter, PROPER is only a first step, and you must correct the exceptions.

Making sentence case with a formula

There is no function for sentence case, but a formula gives it: =UPPER(LEFT(A1,1))&LOWER(MID(A1,2,LEN(A1))). It takes the first letter and converts it to a capital, takes the rest from the second character on and converts it to lowercase, and joins the two parts with the & operator. It works well for a cell with one sentence.

For cells with several sentences, a formula becomes complicated, and you are better off pasting the cells into a converter, one cell per line, choosing Sentence case, and pasting the result back. In Google Sheets, a regular expression with REGEXREPLACE can capitalize the first letter of each sentence, but the formula is hard to read and to maintain.

Function names in other languages

Excel uses localized function names, so a formula copied from an English guide may not work in another language version. In German, UPPER is GROSS, LOWER is KLEIN and PROPER is GROSS2. In French, they are MAJUSCULE, MINUSCULE and NOMPROPRE. In Spanish, they are MAYUSC, MINUSC and NOMPROPIO. Google Sheets uses the English names in all languages, which makes sheets easier to share.

If a formula shows the error #NAME?, the function name is probably not recognized in your language version. Use the Insert Function dialog, which shows the local names, or switch your formulas to the local names.

Flash Fill and Power Query

Excel has two other ways to change case. Flash Fill, available since Excel 2013, learns from an example: type the converted form of the first value in the next column, start typing the second, and press Ctrl+E to fill the rest. It is quick, but it guesses, so check the results.

Power Query, which is built into recent versions of Excel as Get & Transform, has a Format menu in the Transform tab with lowercase, UPPERCASE and Capitalize Each Word. These use the functions Text.Lower, Text.Upper and Text.Proper. Power Query is the better choice when the data is large or when you will refresh the import regularly, because the steps are recorded and repeated.

Case-sensitive and case-insensitive comparisons

Excel's equals operator is not case-sensitive: =A1=B1 returns TRUE for Apple and APPLE. To compare with case, use EXACT(A1,B1), which returns TRUE only if the two texts are identical, including case. In the same way, SEARCH finds text without regard to case, and FIND is case sensitive. SUBSTITUTE is case sensitive as well, which surprises many users when a replacement does not hit every spelling.

For removing duplicates, Excel's Remove Duplicates command ignores case, which is usually what you want for names and emails. COUNTIF and VLOOKUP also ignore case. If you need case-sensitive matching, build it on EXACT or use an array formula. These rules are the same in Google Sheets.

When to use a converter instead

A converter is faster than formulas for one-off work. Copy a column from the sheet, paste it into the converter, choose the style, and paste the result back. This is also the easier route for Title Case with small words, Sentence case and the programming styles, which Excel does not offer at all.

Keep the formulas for data that changes or that is used inside a bigger calculation. Use the converter for a clean-up that you do once. And in both cases, scan the result for names, acronyms and brand names that were changed by mistake.

Common mistakes to avoid

The most frequent mistake is to apply PROPER to a column of names and publish the result without a look. Surnames with prefixes, hyphenated names and acronyms need manual correction, and a quick filter for values that contain an apostrophe, a hyphen or consecutive capitals finds most of them. Another mistake is to overwrite the source data with converted values before checking them. Keep the original column until the work is verified, and write the converted text into a new column so that you can always go back and compare the two versions side by side.