In this post, you will learn how to convert a text to sentence case in Excel.
You can download the workbook used in this post from here – click here to download
Setting text to sentence case –
Excel provides three useful functions to change the text to upper, lower, or proper case. As
you can see in rows 6, 7, and 8 illustrated in Figure below, these functions require nothing more than a pointer to the text that you want to be converted. As you might guess, the UPPER function converts text to all uppercase, the LOWER function converts text to all lowercase, and the PROPER function converts text to title case (the first letter of every word is capitalized).
What Excel lacks is a function to convert text to sentence case (where only the first letter
of the first word is capitalized). But as you can see in Figure above, you can use the following
formula to force text into sentence case:
=UPPER(LEFT(C4,1))&LOWER(RIGHT(C4,LEN(C4)-1))If you take a look at this formula closely, you can see that it’s made up of two parts that
are joined by the ampersand.
The first part uses Excel’s LEFT function.
UPPER(LEFT(C4,1))The LEFT function allows you to extract a given number of characters from the left of
a given text string. The LEFT function requires two arguments: the text string you are
evaluating and the number of characters you need to be extracted from the left of the text
string. In this case, we are extracting the left one character from the text in cell C4. We are
then making it uppercase by wrapping it in the UPPER function.
The second part is a bit trickier. Here we are using Excel’s RIGHT function:
LOWER(RIGHT(C4,LEN(C4)-1))Like the LEFT function, the RIGHT function requires two arguments: the text you are
evaluating and the number of characters you need to be extracted from the right of the
text string. In this case, however, we can’t just give the RIGHT function a hard-coded
number for the second argument. We have to calculate that number by subtracting 1 from
the entire length of the text string. We subtract 1 to account for the first character, which
is already uppercase thanks to the first part of the formula.
The LEN function is used to get the entire length of the text string. We subtract 1 from
that, and we have the number of characters needed for our RIGHT function.
We can finally pass all of that to the LOWER function to make everything but the first char-
acter lowercase.
Joining the two parts together gives us our sentence case:
=UPPER(LEFT(C4,1))&LOWER(RIGHT(C4,LEN(C4)-1))