Fortunately, you can use a compound formula to have Excel automatically convert your text to sentence case. This formula is made up of different functions, since there’s no exclusive Excel function for this purpose.
Case Conversion in Excel
There are three native functions in Excel for case conversion. These functions take in the text, change the casing and then output it.
UPPER: Converts the text to UPPER CASE. LOWER: Converts the text to lower case. PROPER: Converts the text to Proper Case or Title Case.
As you may have noticed, there’s no Excel function for converting text to sentence case. Luckily, the mentioned functions give us the right ingredients to build a formula that converts text to sentence case in Excel.
In sentence case, the first letter of the text is in uppercase, while the rest are in lowercase. Of course, exceptions such as names and pronouns exist, but we’re going to omit those for now.
Converting Text to Sentence Case in Excel
To create a formula for this purpose, we’re going to make use of five functions. We’ll use the UPPER and LOWER functions in Excel to change the casing. We will also use the LEFT, RIGHT, and LEN Excel functions to indicate which letters should be upper or lower case.
As an example, in this spreadsheet, we’ve got a sentence in cell A2, which clearly isn’t in sentence case. The objective here is to write a formula that converts the text from A2 to sentence case and displays it in D2. Let’s get to it.
Select the cell where you want to display the output. We’re going to select D2. In the formula bar, enter the formula below: =UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1)) Press Enter.
Once you press Enter, Excel will take in the sentence from A2, make it a sentence case, and then display it in D2, where you entered the formula. You can use the fill handle to use the formula for multiple cells, and that’s it. However, if you’re wondering how this formula works, the next section is for you.
Breaking Down the Formula
This formula uses five different functions to convert text into sentence case. The functions and what they do, are as below:
LEFT: Returns a specified number of characters from the start of the string or text. RIGHT: Returns a specified number of characters from the end of the string or text. LEN: Counts the length of a string in characters. UPPER: Converts the string to upper case. LOWER: Converts the string to lower case.
Now take a look at the formula, and you’ll probably be able to tell how the formula works by yourself:
This formula consists of two parts. The UPPER function before the ampersand, and the LOWER function after it. At first, LEFT looks at the A2 string and returns letters 0 to 1, which is the first letter. Then the UPPER function makes this letter upper case.
Next, LEN counts the characters in A2 and subtracts 1 from it (because the first letter of the string should stay upper case), then feeds the resultant number to the RIGHT function. RIGHT takes in A2, and returns all the letters except the first one. Finally, the LOWER function converts the letters to lower case.
Ultimately, UPPER is deployed on the first letter of the sentence, and LOWER is deployed on everything else, and thus, the string is converted into sentence case.
Let Excel Handle It
In some cases, like converting text to sentence case in Excel, there’s no built-in function that does what you have in mind. However, that doesn’t mean that you have to give up on automation in Excel. You can combine different functions and create a formula that does exactly what you want it to do.
