This article shows how to change sentence case in Excel. Though there is no desiccated to accomplish this task by default, you can apply the combined formula, Flash Fill tool, and VBA code. Here, we’ll walk you through six simple and convenient ways to change sentence case in Excel.
Download Practice Workbook
You may download the following Excel workbook for better understanding and practice yourself.
What Is Sentence Case?
You may be curious to know what the sentence case actually is. Don’t be upset if you don’t have any idea about it.
The sentence case is one type of letter case that we are using frequently. In sentence case, the initial letter of the first word should be in capital letters, while the other letters of the first word and all other words in the sentence should be in lower case. For example:
This sentence is written in sentence case.
6 Methods to Change Sentence Case in Excel
Here, we have an Incorrectly Formatted Sentence List containing some Sentences with the improper capitalization of letters.
We wanna convert the casing of those sentences to sentence case. So, without further delay, let’s jump into the handful of methods to change sentence case in Excel.
1. Using Formula Combining UPPER, LOWER, RIGHT, LEFT and LEN Functions
In our first method, we’ll use a formula to change the given incorrect sentences into sentence cases. Follow our steps below.
- At first, select cell C5. Then, write down the formula below and press ENTER.
Here, B5 represents the first sentence in the Incorrect Format Sentence List.
- In the formula, the LEN function determines the total length of the string in cell B5. Then subtract 1 from this length.
- The RIGHT function returns the last characters of the text string in cell B5. Here, the number of characters returned by the RIGHT function is determined by the output of the LEN function.
- After that, get these returned values into the LOWER function to convert the characters to lower case.
- Later, another portion of the formula is concatenated with an ampersand (&).
- Then, the LEFT function returns the first character of the text string in cell B5.
- Finally, the UPPER function converts this first character to a capital letter.
- Secondly, use the Fill Handle tool and drag it down to cell C14 to get the remaining results.
2. Using Formula Combining UPPER, LOWER, MID and LEFT Functions
In our second method, we’ll apply another formula combining some functions. Follow the steps carefully.
- At first, select cell C5 and type in the formula below. Then, press the ENTER key.
Here, B5 serves as a sentence in the Given Case.
We used a new function called the MID function here. In our previous method, we used the RIGHT and LEFT functions which return the characters of a text string from the start and end of the string respectively. But the MID function can return characters from any position in the middle of the string.
3. Engaging Word to Change Sentence Case in Excel
In this method, we’ll get the help of another Office software named Word. With a combination of Excel and Word, we’ll solve the problem in another way. Follow the steps carefully.
- At first, select cells in the B5:B14 range. Copy these cells using the CTRL + C keyboard shortcut.
- Now, open Word and paste those cells into it.
- Secondly, select the whole table in Word. Then, go to the Home tab and select Change Case in the Font group > lowercase.
- At this point, all of our sentences convert into lower case.
- Again, go to the Home tab. Then, select Change Case > Sentence case.
- Finally, all the sentences convert into sentence cases in Word.
- At last, copy the whole table into Word. Then, paste it into the cells in the C5:C14 range.
Note: Don’t convert it to sentence case directly in Word. At first, change it to lower case, then to sentence case. Otherwise, the capital letters in the middle of sentences won’t be changed.
4. Implementing Flash Fill to Change Sentence Case in Excel
Using the Flash Fill tool is another way to change sentence cases in Excel. Follow the steps below.
- At first, select cell C5. Then, type in the formula below and press ENTER.
Here, B5 represents the first sentence in the Given Case.
At this moment, all of the sentences convert into lower case.
- Secondly, in cell D5, write down the first sentence in sentence case manually.
- Thirdly, select cells in the D5:D14 range. Then, go to the Home tab and select Fill in Editing group > Flash Fill from the drop-down.
- However, all of the sentences convert into sentence case.
Note: Don’t convert it to sentence case directly while using Flash Fill. At first, change it to lower case, then to sentence case. Otherwise, it couldn’t work properly.
5. Applying the VBA Code to a Selected Range of Cells
Applying the VBA code is always an exciting alternative. Please follow our steps below.
- At very first, select the cells in the B5:B14 range. After that, right-click on the Sheet Name and select View Code.
- At this point, the Microsoft Visual Basic for Applications window opens. From Toggle Folders, right-click on Sheet6 (VBA 1) > select Insert > Module.
- It opens a code module, where paste the code below.Then, click on the Run button or press the F5 key.
Sub ConvertSelectionToSentenceCase() Dim rng As Range For Each rng In Selection If Application.WorksheetFunction.IsText(rng) Then rng.Value = UCase(Left(rng, 1)) & LCase(Right(rng, Len(rng) - 1)) End If Next End Sub
- Now, close the code module and return to the worksheet. However, you will be amazed to see that the cells of column B are automatically converted to sentence case.
6. Employing the VBA Code to Create a Custom Function
In our above procedure, we selected the range of cells. Then, we applied the VBA code to convert the text strings into sentence cases. But, in this method, we will create a user-defined function using the VBA code. After that, with this function, we’ll change the text strings into sentence cases. So, follow the steps below.
- At first, right-click on the Sheet Name and select View Code.
- At this moment, the Microsoft Visual Basic for Applications window opens. Now, go to the Insert tab and select Module.
- It opens a code module where you need to paste the below code. Then click on the Save icon to save the workbook in Macro-Enabled format.
Public Function SentenceCase(Text As String) As String SentenceCase = UCase(Mid(Text, 1, 1)) & LCase(Mid(Text, 2)) End Function
- After that, return to the worksheet. Then, click on cell C5 and write down =sen. However, you’ll see the SentenceCase function in the suggestion. Later, press the TAB key on the keyboard to select the function.
- Lastly, give the cell reference B5 as the argument of this function and press ENTER.
The text string is changed to sentence case now.
Thank you for reading this article, we hope this was helpful. Please let us know in the comment section if you have any queries or suggestions. Please visit our website ExcelDemy to explore more.