How to Change Sentence Case in Excel (6 Ideal Examples)

Get FREE Advanced Excel Exercises with Solutions!

When you are working with addresses or customer/employee names, then you may need to change the sentence case into other cases. This article shows how to change sentence case in Excel.

Below, you can see an example of changing the sentence case into other cases using Excel formulas.

How to Change Sentence Case in Excel

Here, we will use the Excel Microsoft 365 version for conducting the session.


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 lowercase. For example:

This sentence is written in sentence case.


How to Change Sentence Case in Excel: 6 Ideal Examples

We will use two sample datasets for your better understanding. In one dataset, there are words formatted with the sentence case. In another dataset, there are incorrectly formatted sentence lists.

We want to convert the casing of those sentences into other cases. Here, we’ll walk you through six simple and convenient ways how to change sentence case in Excel. Furthermore, there are more ways to make the random format in sentence case.

So, without further delay, let’s jump into the handful of methods on how to change sentence case in Excel.


1. Changing Sentence Case into Title Case: Using PROPER Function

Here, we will change the sentence case into the title case (capitalize each word). Basically, Excel has a built-in function to do that. Which is the PROPER function.

  • In the C5 cell >> write the following formula.
=PROPER(B5)

This function will capitalize each word of the B5 cell.

Using PROPER Function for Changing Sentence Case into Title Case

  • Now, drag the Fill Handle icon to paste the used formula respectively to the other cells of the column.

Using Fill Handle to Copy same formula

If you don’t want to create a new column for the formatted words, then follow the below procedures. Say, I want to keep the formatted words in the B column not in the C column. Here, if you have lots of filled columns along with the unformatted words, then go to a cell next to the unformatted words >> right-click on that cell >> from the Context Menu Bar >> select Insert >> from Insert dialog box >> choose Entire column.

  • First, select the formatted words >> from the Home tab >> click Copy.

Copy the formatted text

  • Right-click on C5 cell >> from the Context Menu Bar >> go to Paste Options >> select Values (V).

Paste only values

  • Now select all the formatted words >> press CTRL+X to cut them >> go to B5 cell >> press CTRL+V to paste them.

Cut the values of column C and paste them into column B

Finally, you will get all the formatted words in the B column.

Pasted values in B column


2. Converting Sentence Case to Lowercase: Use of Excel LOWER Function

Here, we will change the sentence case into lowercase. Excel also has a built-in function to do that. Which is the LOWER function.

  • In the C5 cell >> enter the following formula.
=LOWER(B5)

This function will lower each letter of the B5 cell.

Using LOWER Function for Changing Sentence Case into Lowercase


3. Changing Sentence Case to Uppercase: Use of Excel UPPER Function

Now, we will change the sentence case into uppercase. Excel has another built-in function to do that. Which is the UPPER function.

  • First, go to the C5 cell >> use the following formula.
=UPPER(B5)

This function will capitalize each letter of the B5 cell.

  • Press ENTER >> get the formatted words.

Using UPPER Function for Changing Sentence Case into Uppercase

Read More: How to Make First Letter of Sentence Capital in Excel


4. Applying Flash Fill to Change Sentence Case in Excel

There is a way to change the text format without any Excel formulas. In this example, we are going to use the Flash Fill feature of Excel. Basically, we have to create a pattern then Excel will auto-apply that pattern to the other values.

  • Write the B5 cell value (Harry potter) in uppercase in the C5 cell >> then go to C6 cell >> write “PE(starting letters of Peter parker).
  • After that, you will get the suggestion from Excel >> press ENTER.

Applying Flash Fill to Change Sentence Case

Similarly, you can use the Flash Fill feature for title case/ lowercase also.

Note: You can apply this method only when you have some formatted words. Actually, with this method, you can change the format from one to another.

Applying Flash Fill to Change Sentence Case into Title Case

Here is another way to use the Flash Fill feature.

  • First, go to C5 cell >> write the uppercase format of the B5 cell value (HARRY POTTER) >> drag the Fill Handle icon.

Dragging Fill Handle

  • After dragging the Fill Handle icon, you will find all the cells having the value “HARRY POTTER” >> then from Auto Fill Options (this option will be available immediately after dragging the Fill Handle icon) >> press Flash Fill.

Using Auto Fill Options in Excel

Note: To change a random text (without format) into a sentence case, you must create a pattern first. So, use any formula (LOWER/UPPER) and then use Flash Fill to make the sentence case.

5. Change Sentence Case in Large Dataset Using Power Query

In Power Query, there are some built-in options to change the sentence format. So, we will use the Power Query to change the sentence case into other cases.

  • Select any cell of the target column >> from the Data tab >> click on From Table/Range.
  • As a result, you will get a dialog box named Create Table >> press OK to that. Here, you will notice that the range and “My table has headers” are auto-filled. Otherwise, manually select the data range, and check “My table has headers”.

Create an Excel table to open Power Query Editor

Then, you will get the Power Query Editor.

  • In Power Query Editor >> go to Add Column >> from Format >> choose your preferred way.

Adding Column with a new format in Power Query

Here, you go. I have changed the sentence case into other cases with this Power Query. Now, it’s time to load them into the Excel worksheet.

  • So, go to the File tab >> from Close & Load >> select Close & Load To.

Close and Load the query in worksheet

  • Then press OK on the Import Data dialog box.

Pressing OK to Import Data dialog box

Below, you can see the result in a new sheet.

Change Sentence Case into Other Cases with Excel Power Query

Read More: How to Change Case for Entire Column in Excel


6. Using Microsoft Word to Change Sentence Case in Excel

You can also use Microsoft Word to change the sentence case in Microsoft Excel.

  • Select the data >> press CTRL+C to copy the data.

Copy the data with sentence case

  • Then, open a blank file of Microsoft Word >> press CTRL+V >> select the pasted data.
  • After that, from the Home tab >> go to Font group >> from Change Case option >> select your preferred case.

Using Microsoft Word to Change Sentence Case in Excel

As I have chosen to Capitalize Each Word, so I got the following output.

Changed format of Microsoft Word

  • Now, copy the formatted data from Microsoft Word >> paste it into Excel.

Using Microsoft Word to Change Sentence Case into other cases

Note: If you need to convert a random unformatted text to sentence case, then at first, change it to lowercase >> then to sentence case. Don’t convert it to a sentence case directly in Word. Otherwise, the capital letters in the middle of sentences won’t be changed.

Read More: Change Upper Case to Lower Case in Excel


How to Change Random Text Case into Sentence Case in Excel

In our other dataset, we have an incorrectly formatted sentence list containing some sentences with the improper capitalization of letters. Now, we will convert the casing of those sentences to sentence cases.


1. Combining Some Excel Functions to Get Sentence Case

Here, we will combine some functions to do that. We can use two different combinations of Excel UPPER, LEFT, LOWER, RIGHT, MID, and LEN functions. Now, see the following formulas for converting an unformatted sentence into a sentence case.


1.1 Combining UPPER, LOWER, RIGHT, LEFT & LEN Functions to Get Sentence Case

  • 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 from the incorrect format sentence list.

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

Combining UPPER, LOWER, RIGHT, LEFT & LEN Functions to Get Sentence Case

Formula Breakdown

  • In the formula, the LEN function determines the total length of the string in cell B5.
    • Output: 36.
  • The RIGHT function returns some defined last characters of the text string in cell B5. The number of characters will be (36-1).
    • So, RIGHT(B5,36-1) gives “ picTUre is worth A ThouSand wORds.
  • After that, get these returned values into the LOWER function to convert the characters to lowercase.
    • Output: picture is worth a thousand words.
  • Then, the LEFT function returns the first character of the text string in cell B5.
    • Output:a“.
  • Later, the UPPER function converts this first character to a capital letter.
    • Output:A“.
  • Finally, both portions of the formula are concatenated with an ampersand (&).
    • Output:A picture is worth a thousand words.“.


1.2 Using UPPER, LOWER, MID & LEFT Functions

In our second method, we’ll apply another formula combining some previous functions along with the MID function.

  • 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 an unformatted sentence.

  • Use Fill Handle for other cells.

Combining Excel Functions to Make Sentence Case

Formula Breakdown

  • In the formula, the LOWER function converts the characters of cell B5 to lowercase.
    • Output: “a picture is worth a thousand words.”.
  • The MID function returns the rest of the characters from 2nd one of the text strings in cell B5.
    • Output: picture is worth a thousand words.
  • After that, get these returned values into the LOWER function to convert the characters to lowercase.
  • Then, the LEFT function returns the first character of the text string in cell B5.
    • Output:a“.
  • And the UPPER function converts this first character to a capital letter.
  • Lastly, the ampersand (&) sign will connect both portions.

Read More: How to Change Case in Excel Sheet


2. Applying VBA Codes to Get Sentence Case

If you are wondering how to change a sentence case in Excel, applying the VBA code is always an exciting alternative. Here, we will attach two VBA codes to get the sentence case.


2.1 VBA Code to a Selected Range of Cells

  • First, select the cells in the B5:B14 range >> from the Developer tab >> select Visual Basic.

Opening Microsoft Visual Basic Editor

  • At this point, the Microsoft Visual Basic for Applications window opens. From the Insert tab >> choose Module.
  • It opens a code module, where you paste the code below.
  • Then, click on the Run button or press the F5 key.

Applying VBA Code to Get Sentence Case

Sub ConvertSelectionToSentenceCase()
Dim my_Range As Range
For Each my_Range In Selection
If Application.WorksheetFunction.IsText(my_Range) Then
my_Range.Value = UCase(Left(my_Range, 1)) & LCase(Right(my_Range, Len(my_Range) - 1))
End If
Next
End Sub
  • You will be amazed to see that the cells of column B are automatically converted to sentence cases.

Run the Code to convert the format in Sentence Case


2.2 VBA Code to Create a Custom Function for Changing Sentence Case

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.

VBA Code to Create a Custom Function for Changing Sentence Case

  • So, insert another Module >> use the following code in that Module >> Save the code >> this will create a function named SentenceCase.
Public Function SentenceCase(Text As String) As String
SentenceCase = UCase(Mid(Text, 1, 1)) & LCase(Mid(Text, 2))
End Function
  • Then, return to the worksheet. Click on cell C5 >> 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. This function will take the text as the argument.
  • So, we give the cell reference B5 as the argument of this function and press ENTER.

Frequently Asked Questions

1. Why is sentence case used?

Sentence case makes text easier to read as it nearly resembles the normal capitalization patterns used in written language. Sentence case provides a standardized format, ensuring that all sentences or text entries begin with a capital letter and the rest are in lowercase.

When exporting or transferring data from Excel to other applications or platforms, sentence case is often the preferred capitalization style. It ensures that the data remains consistent and compatible across different systems.

2. Can I use conditional formatting to automatically convert text to sentence case in Excel?

No, conditional formatting in Excel is primarily used to apply to format based on certain conditions or rules, such as changing cell background color, font color, or adding borders. It doesn’t have the capability to directly change the capitalization of text or convert it to sentence case.

3. How do you edit sentences in Excel?

Go to that cell containing the sentence >> use Formula Bar to edit. Or, double-click on that cell >> make your target changes to that sentence.

4. How do you show full sentences in Excel?

Select that cell >> from the Home tab >> go to Alignment group >> press Wrap Text.


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to change sentence case in Excel


Download Practice Workbook

You may download the following Excel workbook for better understanding and practice yourself.


Conclusion

Thank you for reading this article, we hope this was helpful to teach you how to change sentence case in Excel. Please let us know in the comment section if you have any queries or suggestions.


Related Articles


<< Go Back to Change Case | Text Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shahriar Abrar Rafid
Shahriar Abrar Rafid

Shahriar Abrar Rafid, BSc, Naval Architecture and Marine Engineering, Bangladesh University of Engineering and Technology, Bangladesh, has worked with the ExcelDemy project for more than 1 year. He has written over 100+ articles for ExcelDemy. He is a professional visual content developer adept at crafting scripts, meticulously editing Excel files, and delivering insightful video tutorials for YouTube channels. His work and learning interests vary from Microsoft Office Suites and Excel to Data Analysis, VBA, and Video recording and... Read Full Bio

2 Comments
  1. I am facing a problem,trying to solve it, but couldn’t.
    In first column i have some names of sales person. There are repeated names there. In second column, i’ve their sales amount. From the data table, iwant to find the 3 top selling salesperson name with their sales.I used vlookup, but can’t get the right result.can you help me please?

    • Hello HANNA,
      First of all, thanks for your valuable comment. I think I have got your problem. Follow the steps below.
      Here’s a sample and simple dataset that I created from your query.

      Now, using this dataset, we’ll pick the top 3 Salesman names and their corresponding sales amount.
      • At first, go to cell B16 and enter the following formula.
      =INDEX($B$5:$B$13,MATCH(LARGE($C$5:$C$13,1),$C$5:$C$13,0))
      • Then, press ENTER.

      • After that, bring the cursor to the right-bottom corner of cell B16 and you will find the Fill Handle tool visible.
      • Now, drag the tool up to cell B18.

      In the following cells of Column B, change the k argument of the LARGE function according to the position. See the image below.

      • To obtain their sales amount, go to cell C16 and insert the formula below.
      =LARGE($C$5:$C$13,1)
      • As usual, tap ENTER.

      You can add an extra column just beside the dataset to show the rank of the salesmen according to their sales amount. The formula used in cell D5 is the following.
      =RANK(C5,$C$5:$C$13)

      That’s all from me on this topic. Hope you find this helpful. Follow our website ExcelDemy to explore more about Excel. Happy Excelling.
      Regards
      SHAHRIAR ABRAR RAFID
      Excel & VBA Content Developer
      Team ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo