ExcelInsider Team
Consistency in text formatting is crucial when working with data in Microsoft Excel, particularly when dealing with names, titles, addresses etc. Capitalizing the initial letter of each word in a cell is a frequent formatting requirement for an Excel user. This style is sometimes known as the title case or proper case. Luckily, Excel has several built-in features that makes achieving this style very easy.
➤ Create a separate column to apply the formula and get the output.
➤ Click on the adjacent cell(C2) of the first cell(B2) and insert the formula: =PROPER(B2).
➤ Click and drag the fill handle to apply the formula to the whole column.
In this article, we have included three different methods to capitalize the first letter of each word in a cell. You will find a detailed step-by-step guide on using the PROPER function, Flash Fill and VBA Macro to achieve the desired results.
Use PROPER Function to Capitalize First Letter of Each Word
This is the best possible method in Excel for quickly capitalizing the first letter of each word in a cell. It is very simple and easy to use. If you’re in a hurry to get the job done, use this method without any worries.
This is our sample dataset where the words in each cell of the second column do not follow any specific case order.
We need to format them so that each word starts with a capital letter automatically and then the rest are lowercase as usual.
➤ Start by naming a separate column C as Cast where the formatted names will be shown.
➤ Click on cell C2 and insert the PROPER function:
➤ Even though the word in cell B2 was all uppercase, now the output will contain capital letters for the first letter of each word and the rest will be lowercases.
➤ Click and drag the fill handle until cell C9 to apply the formula for all cells in column B.
Now, we will have the output we desired with the first letter of each word capitalized.
Trigger Flash Fill to Capitalize First Letter of Each Word
Another very simple and fast method is using the Flash Fill feature of Excel for capitalizing first letters. This feature can quickly apply formatting to the entire column based on patterns. You don’t have to use any formulas to do this.
Anyone using Excel 2013 and later versions can use the steps below to utilize Flash Fill.
➤ Start by naming a separate column C as Cast where the formatted names will be shown.
➤ In cell C2, manually type the properly formatted version of the first entry. In this case it should be Al Pacino where each first letter is uppercase. Press Enter.
➤ Click on cell C3 and press CTRL + E to trigger the Flash Fill feature manually. Excel will automatically fill up the rest of the column with the correct formatting.
Use Custom VBA Macro to Capitalize First Letter of Each Word
Using VBA (Visual Basic for Applications) in Excel is another useful method for this when you need to automate tasks or customize functionality. Moreover, VBA is very helpful when you have a large dataset in which you need to do the correct formatting.
Here is a detailed guide on how you can use the VBA Macro to do the proper formatting:
➤ Open your Excel Workbook and press ALT + F11 to open the Visual Basic Editor window.
➤ Right click on the worksheet containing your data then go to Insert > Module.
➤ In the new module, copy and paste the following code.
Function CapitalizeWords(rng As Range) As String
Dim txt As String
txt = LCase(rng.Value) ' Convert to lowercase first
txt = WorksheetFunction.Proper(txt) ' Capitalize each word
CapitalizeWords = txt
End Function➤ The code will look like this in the module:
➤ From the Run tab, select Run Sub/UserForm or press F5 to activate the macro.
➤ In the pop-up box, name the Macro as CapitalizeEachFirstLetter and click Create. Once you’re done, close the Visual Basic Monitor by clicking the red cross in the corner.
➤ Back in the worksheet, in cell C2, input the formula =CapitalizeWords(B2) now and it will give the correct formatting.
➤ Click and drag the fill handle until cell C9 to apply the formula for all cells in column B.
➤ Now, we will have the output we desired with the first letter of each word capitalized.
Frequently Asked Questions
Will the PROPER Function Format All Uppercase, All Lowercase or Mixed Cases?
Yes, the PROPER function handles and perfectly formats the words in a cell so that each word starts with a Capital letter and the rest are small letters. For instance,
All uppercase: AL PACINO→ Al Pacino
All lowercase: samuel jackson→ Samuel Jackson
Mixed cases: brad piTT→ Brad Pitt
When Should I Use VBA Instead of Excel Formulas to Capitalize First Letters?
Instead of regular Excel formulas, you should go for VBA when-
- You require automation for repetitive tasks and entries.
- Needing more control and customization over the formatting.
Why Can’t I Save the Excel File After Using VBA?
You won’t be able to save the Excel workbook as the regular .xlsx format after incorporating VBA. First, you need to enable macros when the prompt shows up. Then you need to save the workbook as Macro-Enabled Workbook (.xlsm) format on your device.
Wrapping Up
Throughout this guide, we explored several effective ways to capitalize the first letter of each word in Excel. We began by utilizing the built-in PROPER function, which is ideal for employing a straightforward formula. We also talked about other approaches, including Flash Fill- which automatically finds patterns, and VBA Macro for users who require more adaptable solutions.
Find the method you are the most comfortable with and start working on your worksheets.
