Excel is the most widely used tool for dealing with massive datasets. We can perform myriads of tasks of multiple dimensions in Excel. In order to arrange the dataset for our convenience, we often need to modify the cases of texts. Excel offers us various methods to alter the case of a text. In this article, I will show 4 ways to change to Title Case in Excel.
This is the dataset for today’s article. There are some Names but all are in lower case. I will change them to title cases.
1. Using PROPER Function to Change to Title Case in Excel
The first method is the use of the PROPER function. The PROPER function changes the case of a text. It transforms the 1st letter of a word to upper case and others to lower case. The syntax of the function is PROPER (text) where text is required. With this function, we will convert the texts to the title case.
Steps:
- Go to cell C5 and write down the following formula.
=PROPER(B5)
- Then, press ENTER. Excel will show the output.
- After that, use Fill Handle to AutoFill up to cell C10.
Read More: How to Capitalize First Letter of Each Word in Excel
2. Applying Excel VBA Macro to Change to Title Case
Now, I will discuss the use of the VBA Macro to change a text to title case. VBA stands for Visual Basic Application. This is the programming language for Microsoft Excel.
Steps:
- Press CTRL+ C on your keyboard to copy B5:B10.
- Then, press CTRL+ V to paste them in cell range C5:C10.
- Now, press ALT + F11 to bring the VBA.
- Then, go to Insert >> select Module to create a new module.
- A new module will open. Write down the following code in that module.
VBA Code:
Sub TitleCase()
Dim R As Range
Dim Rng As Range
On Error Resume Next
xTitleId = "Select Your Range"
Set Rng = Application.Selection
Set Rng = Application.InputBox("Put the Range", xTitleId, Rng.Address, Type:=8)
For Each R In Rng
   R.Value = Application.WorksheetFunction.Proper(R.Value)
Next
End Sub- Then, press F5 to run the program.
- Alternatively, you can run the program from the ribbon by pressing the Run Sub.
- You will see that an input box will appear.
- Choose your range. Here, it is C5:C10.
- Then click OK.
- Excel will convert the texts to the title case.
VBA Code Explanation
Here, I have created a Sub Procedure TitleCase. Then I have defined two variables R and Rng both of which are Range. Then, I called an Input Box. Finally, for each value of variable R, I have used WorksheetFunction.Proper property to convert the texts to title case.
Read More: Excel VBA to Capitalize First Letter of Each Word
3. Use PowerQuery to Change to Title Case in Excel
In this section, I will demonstrate the use of PowerQuery to change the title case in Excel. PowerQuery is a tool that one can use to import or connect to data from another source and modify them.
Steps:
- Copy the Names and paste them in C5:C10 following method-2.
- Then, select the entire table.
- After that, go to the Data tab >> select From Table/Range.
- The Create Table box will pop up. Check the box if your table has headers.
- Then, click OK.
- Excel will open the Power Query Editor
- Then, select the column named converted.
- After that, go to the Transform tab >> Text Column >> Format >> select Capitalize Each Word.
- You will see that Excel has changed the case.
- After that, go to the Home tab >> select Close & Load.
- Excel will create a new table in a new worksheet.
Read More: How to Change Case in Excel Without a Formula
4. Changing to Title Case with Excel Flash Fill Feature
Now, I will demonstrate another easy method to change a text to the title case in Excel. This time, I will use the Flash Fill feature to do the task. Flash Fill fills datasets automatically upon sensing a pattern.
Steps:
- Write the 1st name in the title case manually in C5.
- Now, as soon as you try to do the same for the 2nd name, you will see that Excel is showing the suggestions while keeping the same pattern.
- Now, press ENTER to let Excel Flash Fill the remaining.
Read More: Excel Change to Proper Case Without Formula
Things to Remember
- You must save the file in Macro-Enabled Workbook with the extension .xlsm.
- The Flash Fill feature will not work if you are using older versions of Excel.
Download Practice Workbook
Download this workbook and practice while going through the article.
Conclusion
In this article, I have demonstrated 4 effective methods to change the title case in Excel. I hope it helps everyone. If you have any suggestions, ideas, or feedback, please feel free to comment below.
