How to Change Sentence Case in Excel (6 Easy Methods)

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.

how to change sentence case in excel dataset

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.

Steps:

  • At first, select cell C5. Then, write down the formula below and press ENTER.
=UPPER(LEFT(B5,1))&LOWER(RIGHT(B5,LEN(B5)-1))

Here, B5 represents the first sentence in the Incorrect Format Sentence List.

Formula Breakdown:

  • 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.

how to change sentence case in excel using formula

  • Secondly, use the Fill Handle tool and drag it down to cell C14 to get the remaining results.

how to change sentence case in excel using formula

Read More: How to Make First Letter of Sentence Capital in Excel (4 Suitable Methods)


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.

Steps:

  • At first, select cell C5 and type in the formula below. Then, press the ENTER key.
=UPPER(LEFT(B5,1))&MID(LOWER(B5),2,999)

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.

how to change sentence case in excel using formula

Read More: How to Change First Letter to Uppercase in Excel (6 Handy Methods)


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.

Steps:

  • At first, select cells in the B5:B14 range. Copy these cells using the CTRL + C keyboard shortcut.

how to change sentence case in excel using Word

  • Now, open Word and paste those cells into it.

how to change sentence case in excel using Word

  • Secondly, select the whole table in Word. Then, go to the Home tab and select Change Case in the Font group > lowercase.

how to change sentence case in excel using Word

  • At this point, all of our sentences convert into lower case.

how to change sentence case in excel using Word

  • Again, go to the Home tab. Then, select Change Case > Sentence case.

how to change sentence case in excel using Word

  • Finally, all the sentences convert into sentence cases in Word.

how to change sentence case in excel using Word

  • At last, copy the whole table into Word. Then, paste it into the cells in the C5:C14 range.

how to change sentence case in excel using Word

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.

Read More: How to Change Case for Entire Column in Excel (7 Amazing Ways)


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.

Steps:

  • At first, select cell C5. Then, type in the formula below and press ENTER.
=LOWER(B5)

Here, B5 represents the first sentence in the Given Case.

how to change sentence case in excel using Flash Fill

At this moment, all of the sentences convert into lower case.

  • Secondly, in cell D5, write down the first sentence in sentence case manually.

how to change sentence case in excel using Flash Fill

  • 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.

how to change sentence case in excel using Flash Fill

  • However, all of the sentences convert into sentence case.

how to change sentence case in excel using Flash Fill

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.

Read More: How to Change Upper Case to Lower Case in Excel (5 Effective Methods)


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.

Steps:

  • At very first, select the cells in the B5:B14 range. After that, right-click on the Sheet Name and select View Code.

Applying VBA Code

  • At this point, the Microsoft Visual Basic for Applications window opens. From Toggle Folders, right-click on Sheet6 (VBA 1) > select Insert > Module.

Applying VBA Code

  • 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

Applying VBA Code

  • 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.

Applying VBA Code

Read More: How to Change Case in Excel Sheet (8 Quick Methods)


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.

Steps:

  • At first, right-click on the Sheet Name and select View Code.

Applying VBA Code

  • At this moment, the Microsoft Visual Basic for Applications window opens. Now, go to the Insert tab and select Module.

Applying VBA Code

  • 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

Applying VBA Code

  • 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.

Applying VBA Code

  • Lastly, give the cell reference B5 as the argument of this function and press ENTER.

Applying VBA Code

The text string is changed to sentence case now.


Conclusion

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.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo