It only takes a single click in Microsoft Word to convert our text to sentence case.
Unfortunately, there is no direct Excel function or Excel button in Microsoft Excel to change the text to sentence case. While there are dedicated functions in Excel to change the text to lower, upper, or proper case, there is nothing for sentence case.
Don’t worry. Thankfully we have some Excel solutions for converting text to sentence case in Excel.
In this tutorial, I’ll teach you how to change to sentence case in Excel using various ways.
Understanding Sentence Case
Sentence case is a writing style where the first letter of the first word in a sentence (and the first letter of proper nouns) is capitalized, and the rest of the letters are in lowercase.
This makes reading easier and gives a polished look to your text.
You might have come across sentence case in books, articles, and documents.
Many popular newspapers, such as The Washington Post or The Los Angeles Times, use the sentence case structure in their headings.
Below is an example of a title in sentence case that I found in the LA Times.
When working with Excel, you might want to convert your text data to sentence case.
This will not only make your data look neat, but it will also improve readability.
Utilizing Excel formulas or built-in features, you can easily transform text into sentence case without needing any prior expertise in programming or advanced Excel functions.
For instance, imagine you have a list of product descriptions, and some are written in uppercase, while others are in lowercase.
By converting the text to sentence case, you can make your product list more consistent and well-organized.
How to Convert to Sentence Case in Excel?
Now let’s have a few methods you can use in Excel to convert text to sentence case in Excel.
Method 1 – Using Flash Fill to Convert to Sentence Case in Excel
Excel’s Flash Fill, in my opinion, is the easiest way to change the text case to a sentence case.
Understanding Excel formulas or functions is not essential for this method to be used.
The image below shows a list of sentences in an Excel sheet.
All of these sentences now need to be changed to “Sentence case.”
You have to follow the simple steps listed below.
- Copy the text in cell A1 and paste it into cell B1.
- Change the first letter of cell B1 to upper case. So, in this example, you have to capitalize the first letter “s”.
- To use the Flash Fill, go to cell B2 and press “Control + E“. Column B will then display all sentences in “Sentence case” format.
Flash Fill works by deciphering patterns in the result that you manually provide. In our example, it was able to identify that we want to capitalize the first letter and leave everything in lowercase. In some cases where Flash Fill is not able to identify the right pattern, it would be a good idea to help it out and provide results in two or three cells.
If you don’t want to use the “Control + E” shortcut to apply Flash Fill, you can use the Flash Fill icon available in the ribbon. It’s in the Home tab >> Editing Group >> ‘Fill’ Drop-down >> Flash Fill. To use this, first, enter the result manually in cell B1, then select the next cell, and click on the Flash Fill icon.
Drawback: One major limitation of converting text to sentence case in Excel is that it will only be able to capitalize the first letter of a new sentence. In case there are nouns (such as names of people or brands, or places), it would make everything lowercase. Unfortunately, this limitation will be there in all the methods covered in this tutorial.
Also read: How to Change All Caps to Lowercase Except the First Letter in Excel?
Method 2 – Using a Formula to Convert Text to Sentence Case in Excel
The previously mentioned Flash Fill approach is really simple to use, but it isn’t always the ideal way to convert to sentence cases in Excel.
Particularly when editing some texts that you have already changed to “sentence case.”
Assume you have updated the numbers in column A of the previous example. Now, if you used the Flash Fill method to convert text to sentence case, new numbers will not be updated in column B.
To solve this issue, we can use Excel functions to convert to sentence case.
Excel does not have a dedicated function for this. But we can combine Excel simple text functions in a number of ways to convert text to sentence case in Excel.
Formula 1 – Using UPPER, LOWER, and LEFT/RIGHT Function
An Excel sheet with a list of sentences is shown in the figure below.
Now we need to convert these text values in the cells into sentence case.
To convert cell A1 to sentence case, use the following formula in cell B1.
=UPPER(LEFT(A1,1))&LOWER(RIGHT(A1,LEN(A1)-1))
The UPPER function is used to convert the first letter of the sentence to uppercase.
In the above example, we only need to change the first letter of the sentence to uppercase. We can use the Excel LEFT function to get the first letter of the sentence, which is then used by the UPPER function to convert it into upper case.
Following that, we have to convert all remaining letters in the sentence to lowercase letters.
We can do this by using the Excel LOWER function. The Excel RIGHT function can be used to extract all letters on the right side of the first letter. The Excel LEN function helps to dynamically figure out the number of characters we want to get from the RIGHT function.
We have now made the first letter of the sentence uppercase and all subsequent letters lowercase. Using the ampersand (&) symbol, we can combine these two.
Also read: How to Change Uppercase to Lowercase in Excel
Formula 2 – Using UPPER, LOWER and MID Function
To change any text to sentence case in Excel, we can replace the RIGHT function in the above formula with the MID function.
An Excel sheet with a list of sentences is seen in the image below.
Now we need to convert these text values in the cells into sentence case.
We can apply the below formula in cell B1 to convert cell A1 to sentence case.
=UPPER(LEFT(A1))&LOWER(MID(A1,2,LEN(A1)))
The UPPER function helps to change the text to upper case or capital letter form.
In this example, we only need to change the first letter of the sentence to uppercase. We can use the Excel LEFT function to dynamically get the first letter of the sentence.
After that, we need to convert all remaining letters in the sentence to lowercase letters. We can use the Excel LOWER function for this.
The Excel MID function can be used inside the LOWER function to extract all letters that are to the eright side of the first letter. We can use the Excel LEN function to count the total number of characters in the text.
You have now made the first letter of the sentence uppercase and all subsequent letters lowercase. We use the ampersand (&) character to connect these two.
Formula 3 – Convert Multiple Sentences in a Single Cell to Sentence Case in Excel
We sometimes have multiple sentences in the same cell.
In that case, the above two formulas are not helpful. We need a more complex formula to identify the initial letter of each sentence in the cell and convert it to upper case.
For this, we can use the Excel SUBSTITUTE function.
A list of sentences is displayed in one cell in the figure below.
Now we need to convert all these sentences to “Sentence case”.
We can apply the below formula in cell B1 to convert cell A1 to sentence case.
=SUBSTITUTE(SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(LOWER(TRIM(A1)),". ","$$9")," ","zzz")),"$$9",". "),"zzz"," ")
In this formula, we must first use the LOWER function to convert all of the letters in the cell to lowercase.
The LOWER(A1) part of the formula gives us the following result.
sales growth is 50%. the total sales amount is $100,000. gross profit margin is 20%. net profit is increased by 15%.
Then, we use the Excel SUBSTITUTE function to replace each full stop with a space “. ” at the end of each sentence with “$$9″ and each space ” ” within the sentence with “zzz.”
You can substitute any other value for $$9 and zzz. However, the “new text value” for the substitute of “. ” should end with a number.
SUBSTITUTE(text, old_text, new_text, [instance_num]) is the syntax for the SUBSTITUTE function. For cell A1, we have to use the result of the LOWER function as the text argument. First, we change the full stop with a space “. ” at the end of each sentence to “$$9”.
So, this is what we come up with:
sales growth is 50%$$9the total sales amount is $100,000$$9gross profit margin is 20%$$9net profit is increased by 15%.
Then we open another SUBSTITUTE function and use the previous SUBSTITUTE function’s result as the text of the second SUBSTITUTE function. Using the second SUBSTITUTE function, change every ” ” in the sentence to “zzz”.
Then we have the following:
saleszzzgrowthzzziszzz50%$$9thezzztotalzzzsaleszzzamountzzziszzz$100,000$$9grosszzzprofitzzzmarginzzziszzz20%$$9netzzzprofitzzziszzzincreasedzzzbyzzz15%.
Next, we use the PROPER function to convert all sentence beginning letters to uppercase. The PROPER function helps in changing the first letter of each word to uppercase.
Excel will consider each letter following a number to be the beginning letter of a new word. As a result, every letter following the $$9 will be changed to uppercase.
Then our formula gives:
Saleszzzgrowthzzziszzz50%$$9Thezzztotalzzzsaleszzzamountzzziszzz$100,000$$9Grosszzzprofitzzzmarginzzziszzz20%$$9Netzzzprofitzzziszzzincreasedzzzbyzzz15%.
The final phase is to restore the original values to the substituted values. So we have to change “$$9” to “. ” and “zzz” to ” “.
So we can apply two more SUBSTITUTE functions to get the original text values.
Then our end result will be;
Sales growth is 50%. The total sales amount is $100,000. Gross profit margin is 20%. Net profit is increased by 15%.
Note:
If you have commas in your text, you are not going to get the spaces right next to the comma when restoring spaces. Instead of a space, you’ll see “Zzz” next to the comma in this example. The reason for this is that Excel considers the first letter following the comma to be the first letter of a new word, and Excel converts the first letter of “zzz” to upper case. The SUBSTITUTE function is case sensitive, so if you try to replace “zzz” with ” ” it won’t work.
The solution is to use another SUBSTITUTE function and change “Zzz” to ” “.
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(LOWER(A1),". ","$$9")," ","zzz")),"$$9",". "),"zzz"," ")=SUBSTITUTE(SUBSTITUTE(PROPER(SUBSTITUTE(SUBSTITUTE(LOWER(A1),". ","$$9")," ","zzz")),"$$9",". "),"zzz"," "),"Zzz"," ")
Alternative Methods for Sentence Case Conversion (Outside of Excel)
Aside from using Excel formulas, there are other ways to convert text to sentence case.
In this section, we will explore two alternative methods: using Microsoft Word and utilizing online tools.
Using Microsoft Word
You can easily convert text to sentence cases in Microsoft Word. It offers a handy built-in tool for this purpose.
Here’s how you can do it:
- Copy the text from Excel and paste it into a Word document.
- Select the text you want to convert.
- Click the “Home” tab on the toolbar, then locate the “Change Case” icon in the “Font” section.
- Click the icon and select “Sentence case” from the dropdown menu.
- Once the text is converted, copy it back to Excel.
While this is not the most elegant way, it may be faster as it doesn’t require you to use formulas or Flash Fill. This would be preferred in cases when you want to change the case to sentence for some sentences.
Utilizing Online Tools
There are various online tools available that can help you quickly convert text to sentence case.
They are user-friendly and don’t require any downloads or installations. Here’s how to use them:
- Find a reputable online text conversions tool, such as Convert Case or Capitalizemytitle.
- Copy the text from your Excel spreadsheet and paste it into the designated input area on the website.
- Select the “Sentence case” option and click the corresponding button to convert your text.
- Once the text has been converted, copy and paste it back into your Excel spreadsheet.
Remember to choose the method that works best for you and your needs. Both Microsoft Word and online tools offer a simple and efficient way to convert text to sentence case without using Excel formulas.
Sentence Case vs. Proper Case
Sentence case refers to capitalizing only the first letter of the first word in a title, along with any proper nouns. All other words are in lowercase.
For example – “The cat in the hat”
Proper case, also known as title case, capitalizes the first letter of every major word in a title, including nouns, verbs, adjectives, and adverbs.
Articles, prepositions, and conjunctions are typically not capitalized unless they are the first or last word in the title.
For example – “The Cat in the Hat”
You have now learned how to convert to sentence case in Excel. You can pick a suitable formula for you or choose the simple flash-fill method, depending on your needs.
It’s important to know that Excel won’t recognize proper nouns for you. Excel does all of these conversions logically.
So you have to read all changed sentences and update the first letter of proper nouns.
Other articles you may also like:
- How to Remove First Character in Excel?
- How to Count How Many Times a Word Appears in Excel (Easy Formulas)
- How to Generate Random Letters in Excel?
- How to Remove the Last Digit in Excel?
- How to Remove Space before Text in Excel
- How to Add Text to the Beginning or End of all Cells in Excel
- How to Remove a Specific Character from a String in Excel