You may have found yourself working with a long list of names or product titles in Excel and thought, “I wish I could make the first letter of each word capitalized.”
Luckily, Excel offers a simple way to capitalize the first letter of each word.
To capitalize the first letter of each word in a cell, you can use the PROPER function in Excel. For instance, if you have a cell with the text “john doe,” you can use the formula “=PROPER(A1)” to capitalize the first letter of each word in the cell.
In this article, we will discuss 5 quick ways you can use to capitalize the first letter in Excel, including using the PROPER function, combining functions like UPPER, LOWER, and MID, and also using the Flash Fill tool.
Let’s dive in!
5 Ways to Capitalize First Letter in Excel
In this section, we will 5 great ways to capitalize the first letter in Excel.
Specifically, we will cover the following:
Using the PROPER Function
Using the Upper, Lower, LEFT, and RIGHT Functions
Using Flash Fill
Using Power Query
Using VBA
Method 1: How to Capitalize The First Letter Using The PROPER Function
The PROPER function in Excel is used to capitalize the first letter of each word in a text string.
It’s particularly useful for formatting names, titles, or other phrases where you want each word to start with a capital letter.
The syntax of PROPER function is:
=PROPER(text)Suppose we have the following dataset:
We want to capitalize the first letter of the Occupations column. To do this, we can use the following formula:
=PROPER(C2)Where C2 is the cell reference.
In Excel, the formula will look like the following:
When you click enter, the respective occupation will be converted to a proper case. You can then drag the fill handle down for each cell to apply the formula to the rest of the column.
Method 2: How to Capitalize First Letter Using Upper, LOWER, LEFT, and RIGHT Functions
To capitalize only the first letter of a string in Excel while ensuring the rest of the string remains in lowercase, you can use a combination of the UPPER, LOWER, and LEFT functions.
This method is particularly useful when you want to ensure that only the very first letter of a string is capitalized, regardless of the word boundaries.
The descriptions of the functions are given below:
UPPER Function: The UPPER function converts a text string to all uppercase letters.
LOWER Function: The LOWER function converts a text string to all lowercase letters.
LEFT Function: The LEFT function returns the first character (or characters) in a text string, based on the number of characters you specify.
RIGHT Function: The RIGHT function is used to get the rest of the string after the first character.
The combined syntax is given below:
=UPPER(LEFT(text, 1)) & LOWER(RIGHT(text, LEN(text) - 1))Let’s say we want to capitalize the first letter of the cities column.
Your formula will look like the following:
=UPPER(LEFT(B2, 1)) & LOWER(RIGHT(B2, LEN(B2) - 1))In Excel, the formula will look like the following:
Click enter, then drag the fill handle down for each cell to apply the formula to the rest of the column.
The first letter of all the cities will be converted to capital letters.
Method 3. How to Use Flash Fill to Capitalize First Letter in Excel
Flash Fill in Excel is a smart tool that automatically fills your data when it senses a pattern in how you are entering information.
It’s a quick and easy way to capitalize the first letter of each string in a column without using formulas.
However, the Flash Fill option works based on recognizing patterns, so you need to manually provide an example of the pattern you want it to follow.
Follow the steps given below to capitalize the first letter using flash fill in Excel:
Step 1: Enter a Manual Example
In the cell adjacent to your first data entry, manually type the text as you want it to appear.
For example, if the first entry in your column is “alice” and you want to capitalize it to “Alice”, type “Alice” in the cell next to it.
Step 2: Initiate Flash Fill
Select the cell where you typed the corrected format.
Go to the Data tab on the Ribbon.
Click on the Flash Fill button in the Data Tools group. Alternatively, you can simply press Ctrl + E.
Excel will automatically fill down the remaining cells based on the pattern you provided.
Review the results to ensure accuracy, as Flash Fill might not always interpret the pattern correctly, especially with more complex data.
Method 4: How to Use Power Query to Capitalize First Letter in Excel
Using Power Query in Excel to capitalize the first letter of text involves transforming the column data.
Power Query is a powerful tool for data transformation and can handle this task efficiently, especially with large datasets.
Follow the steps given below to capitalize the first letter using Power Query:
Step 1: Select Your Data
Highlight the range of your dataset or simply click on a cell within your dataset.
Ensure your data is in a table format. If it’s not, you can convert it by pressing Ctrl + T.
Step 2: Open Power Query
Go to the Data tab on the Ribbon.
Click on From Table/Range in the Get & Transform Data group.
Your data will now open in the Power Query Editor.
Step 3: Transform the Column
Right-click on the column header for which you want to capitalize the first letter.
Select Transform ? Format ? Capitalize Each Word.
This will make the first letter of each word uppercase. However, Power Query will capitalize more than one word with this method in a single cell.
If you need to capitalize only the first letter of the entire string (and not each word), you’ll have to add a custom column.
Step 4: Adding a Custom Column for Specific Capitalization
Go to the Add Column tab.
Click on Custom Column.
In the formula area, use a formula like this:
Text.Upper(Text.Start([YourColumnName], 1)) & Text.Lower(Text.Middle([YourColumnName], 1, Text.Length([YourColumnName])))In the above formula, replace [YourColumnName] with the actual name of your column.
In our case, we will replace it with Cities.
Click OK when you are done writing the formula.
A new column with capitalized city names will be created where the first letter is upper case.
Method 5: How to Capitalize First Letter Using VBA in Excel
To capitalize the first letter of a string using VBA in Excel, you can write a simple macro.
This macro will loop through a specified range and change the first letter of each cell to uppercase, while the rest of the text remains in lowercase.
Follow the steps below to capitalize the first letter using VBA in Excel:
Step 1: Open the VBA Editor
Press Alt + F11 to open the VBA Editor.
Step 2: Insert a New Module
In the VBA Editor, right-click on any of the items in the “Project-VBAProject” pane (usually on the left side).
Choose Insert > Module. This will add a new module to your project.
Step 3: Write the Macro
In the new module, paste the following VBA code:
Sub CapitalizeFirstLetter()
Dim rng As Range
Dim cell As Range
' Define the range you want to modify
Set rng = Selection
' Loop through each cell in the range
For Each cell In rng
If Len(cell.Value) > 0 Then
cell.Value = UCase(Left(cell.Value, 1)) & LCase(Mid(cell.Value, 2))
End If
Next cell
End SubThis macro will capitalize the first letter of each cell in the selected range.
In VBA, the code will look like the following:
Step 4: Run the Macro
Return to Excel and select the range of cells where you want to capitalize the first letter.
Press Alt + F8, select CapitalizeFirstLetter, and then click Run.
This will capitalize the first letter of each cell in the Occupations column.
Learn how to use statistical tests in Excel by watching the following video:
Final Thoughts
Understanding how to capitalize the first letter in Excel is one of the valuable Excel skills that can enhance the professionalism and readability of your data.
By mastering the techniques learned in this article, you can efficiently transform your data to meet your desired formatting standards and improve the overall presentation of your spreadsheets.
Best of luck with your spreadsheets.
Frequently Asked Questions
In this section, you will find some frequently asked questions you may have when capitalizing the first letter in Excel.
How to capitalize the first letter of each word in a cell using a formula?
To capitalize the first letter of each word in a cell, you can use the PROPER function.
For example, if you have a cell with the text “john doe,” you can use the formula =PROPER(A1) to make the first letter capital of each word in the cell.
How to change lowercase to uppercase in Excel?
To change lowercase to uppercase in Microsoft Excel, you can use the UPPER function.
For example, if you have a cell with the text “john doe,” you can use the formula =UPPER(A1) to convert all the letters in all the cells to uppercase.
What is the formula to capitalize the first letter of a sentence?
To capitalize the first word of a sentence, you can use the PROPER function.
For example, if you have excel cells with the text “john doe is a developer,” you can use the formula =PROPER(A1) to capitalize the first letter of each word in the cell.
How to capitalize the first letter in a column of names?
To capitalize the first letter in a column of names, you can use the Flash Fill tool. First, enter the correctly capitalized version of the first name in an adjacent cell.
Then, click on the cell with the correctly capitalized version and press Ctrl + E to activate Flash Fill.
How to capitalize the first letter in a cell without affecting the rest of the text?
If you want to capitalize the first letter in a cell without affecting the rest of the text, you can use a combination of the LEFT, UPPER, and RIGHT functions.
For example, if you have a cell with the text “john doe,” you can use the formula =UPPER(LEFT(A1,1))&RIGHT(A1,LEN(A1)-1) to capitalize the first letter without affecting the rest of the text.
