Working with Excel means dealing with data. As part of your job, you get raw and messy data from different sources. More often than not, you will find that you need to capitalize words during the data cleaning-up procedure. To help you with the process, we will discuss here how to capitalize the first letter of each word in Excel.
Formatting data following a standard style in Excel enhances its looks and adds many functional values. It improves readability and maintains consistency throughout your datasets. But life is not that easy. As an Excel user, you repeatedly find yourself in a place where you get served with cluttered data. Then you clean them up, which takes a noticeable amount of time.
As capitalizing the letters of each word is a part of cleaning up data, our objective for this article is to show you four ways you can do so without spending too much time. The methods we will demonstrate here are unique and suitable for any Excel user. Based on how you work, you can choose the way you like to use and finish your work quickly.
Without wasting any more time, let’s get going!
Contents
Best 4 Ways for Capitalizing the First Letter of Each Word in Excel
Remember that Excel always has some magic whenever you need to do something. You will always have more than one way to accomplish your task. Some processes may even surprise you with how fast yet easy they are!
The situation is no different regarding capitalizing the first letter of each word. We intend to present you with four ways to do this job. And there is something for everyone here. If you are a master of functions, a fan of cheeky Flash Fill, or even a Power Query enthusiast, you will not be disappointed.
Oh, did we even tell you about the VBA approach? We got you covered. And if you are new to the magnificent Excel world, you should study all four methods we will discuss here. Then you can pick your weapon according to your preference.
Let’s explore the methods now!
Method 1: The PROPER Function Approach
For the first method, we will show how you can use the Excel PROPER function to capitalize each word’s first letter. This function is one of the trio of functions that change the case of the text. The other two functions in this triad are the UPPER function and the LOWER function.
The PROPER function has been around since Excel 2007. As you can guess from the name, it changes the text case to a Proper capitalization. It capitalizes all the first letters of every word put through this function.
This PROPER function has a single argument called text. You can put your words in the place of this argument. Better yet, you can have your words in a different cell and use a cell reference for this argument. In this demonstration, we are going this way. Let’s see the process now.
1. First, take an empty column for this operation and click on the first cell. For this demonstration, our dataset has some names, which are all in lowercase. We will capitalize the first letter of each part of the name.
2. After clicking on the empty cell, start writing the formula for the PROPER function, =PROPER(text). Here, we clicked on the adjacent cell with a name in all lowercase.
Afterward, close the parenthesis and press Enter.
3. Now you should see that in the destination cell, the name has all the first letters capitalized.
4. Now fill up the column using the Fill Handle and capitalize the first letters of each word for all the names.
And you are done!
Additional Tip: If you want to eliminate the referencing along with the formula, select all the cells with the PROPER formula, and press the Ctrl+C buttons to copy them. Right afterward, paste the copied texts as Values by pressing the Alt+H+V+V buttons. It will remove the formula you used and only keep the capitalized words.
Method 2: The Flash Fill or CTRL E Approach
The Flash Fill is a handy tool you can find in Excel. It solves several everyday problems of Excel users. This tool is also known for its shortcut, CTRL E. We have a dedicated article on the Flash Fill. Be sure to check it out to learn some productive tricks.
Flash Fill works by picking up patterns. It senses what you are doing in your worksheet. Then when you use this option, it repeats the pattern. For instance, suppose we have a column full of texts with lowercase formatting and want to convert them to UPPERCASE. We can utilize the Flash Fill option to achieve the result by entering the first text in UPPERCASE in the adjacent cell, pressing Enter, and then pressing the Ctrl+E buttons.
We will use this technique to capitalize the first letter of each word in Excel. Let’s see how to do it with a helpful demonstration.
1. Take a new column on the right of your source column. The destination column must touch the source column for Flash Fill to work. Then click on the first empty cell of that new column.
2. Now write words from the first cell and keep the words capitalized. Then press the Enter button.
For example, we took the first name from our dataset. We wrote it with the first letter capitalized in the adjacent empty cell.
3. Right after pressing Enter, press the Ctrl+E buttons on your keyboard. Do not do anything else or click anywhere else between pressing Enter and Ctrl+E.
You should see that the column has been filled with capitalized first letters of each word.
How quick was that?
Method 3: The Power Query Approach
Power in the name, powerful by the game. That is how the Power Query can be introduced to someone new to Excel. It is one of the best tools for proficient users. Sometimes, it is also called “Get & Transform” by Excel enthusiasts.
The Power Query possesses much power to enhance one’s workflow. We will use one part of it to capitalize the first letter of our words. The process may seem comparatively lengthier than the first two methods. However, suppose you do not want to take any extra columns and would love to protect your source data. In that case, the Power Query method will be exclusive to you.
We will explain the process in steps with a convenient demonstration.
1. First, select all the cells with words that you want to capitalize.
2. Now right-click your mouse button on top of your selection. Select the Get Data from Table/Range option from the context menu.
3. You should see a mini Create Table dialogue box. If your selected data has headers (as it does in our demonstration), make sure to put a check on the My table has headers checkbox and click OK.
4. Afterward, you will be taken to the Power Query Editor window. Here, select your table data. You can easily do it by clicking the table header once.
5. Then right-click on the top of your data and click on the Transform option in the context menu. It will slide open a side menu. In it, click Capitalize Each Word.
6. Consequently, you will see that each word’s first letter in your table has been capitalized!
7. Now close the Power Query editor. You will get a confirmation dialogue box. Click on the Keep button.
8. After that, you will be taken back to your Excel workbook. You will see a new worksheet was created within the same workbook. But this new worksheet has your table with capitalized words.
Finito!
You will also see that your original worksheet with the data is still untouched after the process. Since Power Query creates a new sheet with the changed data, it is considered a non-destructive process. Therefore, it is the safest option to capitalize your words if you want to keep the original data intact.
Method 4: The VBA Approach
Visual Basic for Applications (VBA) is THE tool for saving thousands of minutes of your work. With a few lines of code, you can do a job in under five seconds which otherwise would have taken you a few hours to complete.
In Excel, capitalizing the first letter of every word is one of those tasks that can make or break your day. You can spend minutes on the tedious task of capitalizing words following any method. Or you can copy-paste some lines of codes from our article into the VBA editor and complete your work.
We understand that VBA is not everybody’s thing. Some users like using a systematic approach and going through menus to do a particular task. But for those who are okay with VBA, we highly suggest this method for them.
In this section, we will demonstrate two circumstances where you can use the VBA to capitalize the first letters of each word in Excel. We understand that you may need to capitalize words for a range of selections and a whole worksheet. These are the two cases we will talk about here.
4.1: For Range of Selections
Visual Basic for Application (VBA) is nothing less than a magic wand. Whereas you might have had to manually capitalize the first letters of words or follow some lengthy approaches, the VBA lets you do it the fastest way you can. Follow the process for it below.
1. Begin by selecting the cells with words that you want to capitalize. Then go to the Developer ribbon and find the Code group. In it, click on the Visual Basic option. You may also press the Alt+F11 buttons on your keyboard to open the Visual Basic window.
2. Next, the VBA editor window will open. From there, click on the Insert menu first and then click Module from the drop-down list.
3. A Module editor window will open. But now, copy the code from below.
| Sub PROPERCASEforSelectedRange() Dim rg As Range For Each rg In Selection rg.Value = StrConv(rg.Value, vbProperCase) Next rg End Sub |
And paste it into the Module editor. Then click the green Run button marked (2) in the picture below.
Finally, close the VBA editor.
4. You will be taken back to your worksheet. You should see that the words you initially selected have all their first letters capitalized.
All done!
4.2: For a Worksheet
If you have a worksheet full of data where you need to capitalize each word, VBA can save you the pain and do the job quickly. In this section of the article, we will show you the way. We will use a small dataset for this demonstration. However, the process stays the same even if you have thousands of cells with words.
The dataset we will use here is shown below. There are two sets of names in different styles. The goal is to capitalize all parts of the names.
Let’s talk about how you can achieve this result.
1. Keep your worksheet active and go to the Developer ribbon. Now click the Visual Basic option. As a different approach, you can press the Alt+F11 buttons on your keyboard for the same goal.
2. The Microsoft Visual Basic for Applications window will open. In the menu bar, find and click the Insert option. Then from the opened drop-down menu, click the Module option.
3. You will see a Module window at this point. Copy the VBA code from below.
| Sub PROPERCASEforWorksheet() Dim rg As Range For Each rg In ActiveSheet.UsedRange rg.Value = StrConv(rg.Value, vbProperCase) Next rg End Sub |
Now go back to the Module editor and paste the copied code. Click the Run button as marked (2) in the image here. Afterward, close the VBA window.
4. You will see that the first letters of each word in your worksheet have been capitalized.
So swift, isn’t it?
Wrapping Up
The four methods discussed in this article on how to capitalize the first letter of each word in Excel are foolproof. Despite the apparent differences, all the methods here are bound to work perfectly every time you use them.
The first method uses an excellent function called the PROPER function. It is a simple function with a single argument. Excel users of all levels should be able to use it without a problem. The second method is also an easy-to-follow method. The third method follows a protective approach. And finally, the fourth method with VBA is the fastest one you can use to capitalize your words.
We highly recommend reading and practicing all the methods we have discussed here. And once you have successfully done it, you should never have a problem capitalizing the first letters in Excel again!
Similar Post: