Formula Excel : 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 3-2, these functions require nothing more than a pointer to the text you want 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 (only the first letter of the first word is capitalized). But as you can see in Figure 3-2, you can use the following formula to force text into sentence case:

=UPPER(LEFT(C4,1)) & LOWER(RIGHT(C4,LEN(C4)-1))

Image 3-2: Converting text into uppercase, lowercase, proper case, and sentence
case.

How it works

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 extracted from the left of the text string. In this example, you extract the left 1 character from the text in cell C4. You then make it uppercase by wrapping it in the UPPER function.
The second part is a bit trickier. Here, you use the Excel 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 extracted from the right of the text string. In this case, however, you can’t just give the RIGHT function a hard-coded number for the second argument. You have to calculate that number by subtracting 1 from the entire length of the text string. You subtract 1 to account for the first character that is already uppercase thanks to the first part of the formula.

You use the LEN function to get the entire length of the text string. You subtract 1 from that, which gives you the number of characters needed for the RIGHT function. You can finally pass the formula you’ve created so far to the LOWER function to make everything but the first character lowercase.
Joining the two parts together gives results in sentence case:

=UPPER(LEFT(C4,1)) & LOWER(RIGHT(C4,LEN(C4)-1))

Categories: Formula