Excel has three case functions. =UPPER(A1) makes everything capitals, =LOWER(A1) makes everything lowercase, and =PROPER(A1) capitalises the first letter of each word. There’s no sentence-case function, so for that you build it: =UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1)) capitalises the first letter and lowercases the rest.
All four return text in a new cell rather than changing the original, so once you’re happy, copy the results and paste them back over the source as values. Two things worth knowing.
PROPER capitalises after any non-letter, so “o’brien” becomes “O’Brien” (right) but “don’t” becomes “Don’T” (wrong), and it also flattens acronyms — “USA” becomes “Usa”. And an empty cell returns #VALUE! with the sentence-case formula, because LEN(A1)-1 goes negative; wrap it in =IFERROR(...,"") if blanks are possible.
For a one-off conversion with no formula, type the corrected version next to the first row and press Ctrl+E for Flash Fill.
In Excel, there are three primary functions that you can use to change the case for a string.
- UPPER for changing the case to upper.
- LOWER for changing the case to lower.
- PROPER for changing the case where the first letter of each word is in a capital case.
In this tutorial, we will learn to use all these functions to write our formulas.
UPPER CASE
In Excel, there is a function with the name UPPER which allows you to change the case of a string into UPPER (Capital).
In the above example, column A has different strings (text values). And when you use UPPER and refer to the cell, it converts that value into an upper-case text.
=UPPER(A1)LOWER CASE
In the same way, you also have a function with the name Lower to convert a text into a lower-case text.
In the above example, when we use the “Lower” function and get all the characters in the result value in lower-case. This function exactly works opposite the “Upper” function we used in the first example.
=LOWER(A1)PROPER CASE
And then, we have the PROPER function, which converts a text into a proper case where the first letter in each word is a capital letter and the rest are in a small case.
=PROPER(A1)You can see in the above example; the first letter of the word is in a capital case, and the rest are small. And if you have two words, it works in the same way.
And if you want the first letter of the first word in the capital case, and the rest of the letters and all the other words in the small case, you can use the formula below.
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
Written by
Puneet Gogia
Microsoft MVP — Excel · Founder, Excel Champs
Twelve years of teaching Excel, and a decade before that using it as a data analyst. Every tutorial here is written from a file I actually built.