Change Text to Title Case or Capital Case in Excel

Sometimes you may get text data in Excel that is all in uppercase, lowercase, or mixed case as in the example data below:

We need this data in a title case or capital case where the first letter of each word is capitalized. One way to achieve this is to go through each record and manually change the data, but this would be a slow and time-consuming process, especially if you have a large dataset.

Both cases are very similar, and the only difference is that capital letters are used for the first letter of a sentence and for proper nouns.

Title case is used for all words except prepositions, articles, and conjunctions.

In this tutorial we will use the example data to demonstrate how the following 6 better methods can be used to transform the data by changing the text to a title case or capital case:

Method 1: Use the PROPER function

The Excel PROPER function converts a text string to proper case; the first letter in each word to upper case, and all other letters to lower case.

In this approach we use the following steps:

Select Cell B2 and type in the formula =PROPER(A2) as in the example below:

Press the Enter key and drag the fill handle down to copy the formula down the column:

The output shows that the text data in Column A has been changed to title or capital case:

Select the data in range B2:B7 and copy the formula values by pressing Ctrl + C.
Select the range A2:A7 and press Ctrl + Alt + V to launch the Paste Special dialog box. In the dialog box select Values and then press OK:

The formula values will be pasted in Column A as values:

Delete Column B for it is no longer needed.

Method 2: Apply the Flash Fill Feature

The Excel Flash Fill feature automatically fills data when it detects a pattern.

We apply the feature by using the following steps:

Type in a couple of examples of the results we want in Column B as follows:

When we provide enough examples to enable Excel to detect a pattern, it displays the suggested output in light grey font.

Press the Enter key to accept the suggested output:

Alternatively, after entering the example data, highlight it together with the cells that we need to be filled in then go to Data >> Data Tools >> Flash Fill on the Ribbon:

The needed output will be filled in:

We can also the keyboard shortcut Ctrl + E after highlighting the example data together with the cells that we need to be filled in. Excel generates the output we need.

Method 3: Employ Power Query

Power Query is a tool in Excel that we can use to extract data from different sources, transform it, and then load it into a worksheet.

We can use this tool to change the text to title case or capital case by using the following steps:

Select the data that we want to change and go to Data >> Get & Transform >> From Table on the Ribbon:

Mark the My table has headers option and then click OK on the Create Table dialog box that pops up:

Select the Column that contains the data that we want to change, then go to Add Column >> From Text >> Format >> Capitalize Each Word:

This will create a column with the text changed to title case or capital case as follows:

Go to Home >> Close & Load to save changes to the query, close the Query Editor window, and load the results to the default destination:

The query generates the output we need:

We can now delete Column A because we no longer need it.

Method 4: Use Excel VBA

If we have a working knowledge of Excel VBA, we can use the followings steps to achieve the results we need:

Press Alt + F11 to open the Visual Basic Editor (VBE).
Insert a new module by right-clicking the worksheet in the Project Window and then go to Insert >> Module:

In the new module type in the following code:

Sub ProperCase()
    Dim Rng As Range
    Dim WorkRng As Range
    On Error Resume Next
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Range", , WorkRng.Address, Type:=8)
    For Each Rng in WorkRng
        Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
    Next
End Sub

Sub ProperCase()

Dim Rng As Range

Dim WorkRng As Range

On Error Resume Next

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", , WorkRng.Address, Type:=8)

For Each Rng in WorkRng

Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)

End Sub

Save the workbook as a macro-enabled workbook.
Press Alt + F11 to switch back to the active worksheet. Alternatively, click the View Microsoft Excel button:

Press Alt + F8 to launch the Macro dialog box. Alternatively, we can go to Developer >> Code >> Macros on the Excel Ribbon:

In the Macro dialog box ensure that the ProperCase macro is selected and then click the Run button:

Input the data range in the pop-up dialog box by selecting the range that contains the data we want to change and then press OK:

The results we need will be generated:

Method 5: Use Microsoft Word

Excel does not have a Change Case button but another software from Microsoft called “Microsoft Word” has this facility. We can take advantage of it to achieve the results we want by using the following steps:

First, select the entire dataset that we want to transform from the Excel worksheet. Press Ctrl + C to copy the data:

Alternatively, go to Home >> Clipboard >> Copy on the Ribbon to copy the data:

Open Microsoft Word and click the Blank Document option.
Press Ctrl + V to paste the data into Microsoft Word:

Select the data and then first change it to lower case by going to Home >> Font >> lower case:

The data is changed to lower case:

Finally, change the data to the title or capital case by going to Home >> Font >> Capitalize Each Word:

The data will be transformed into the title case:

Select the data and press Ctrl + C to copy the data.
Switch back to Excel and select the input range B2:B7 and paste the data in by pressing Ctrl + V:

Delete Column A for it is no longer needed.

Method 6: Excel Add-in and Custom functions

The free add-in for SEO offers two ways to convert text to capital case or title case.

The first one is to use commands from the ribbon, and the second one is to use custom functions.

Add-in Commands

Let’s look at the first method: commands. If you navigate to SEO >> Text >> Change Case. The top two positions are capital and title cases.

Graphical user interface, application

Description automatically generated

Table

Description automatically generated

Custom Functions

You can also use functions to achieve the same result.

=SEO.CASE.CAPITAL(text)

1	=SEO.CASE.CAPITAL(text)

=SEO.CASE.TITLE(text)

1	=SEO.CASE.TITLE(text)

Conclusion

In this tutorial we have looked at 6 methods that we can use to change the text to title case or capital case in Excel; use the PROPER function, apply the Flash Fill feature, employ Power Query, use Excel VBA, and use Microsoft Word.

Using Excel for the capital case is quite easy, but for the title case you need to take into consideration prepositions, articles, and conjunctions. For that reason, using the add-in is a simpler solution.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Post Views: 428