Future-Proof Your Career, Master Data Skills + AI

Blog

Future-Proof Your Career, Master Data Skills + AI

Blog

How to Capitalize First Letter in Excel: 5 Quick Ways

by | 11:51 pm EST | January 30, 2024 | Excel

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!

How to Capitalize First Letter in Excel

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:

  1. Using the PROPER Function

  2. Using the Upper, Lower, LEFT, and RIGHT Functions

  3. Using Flash Fill

  4. Using Power Query

  5. Using VBA

5 ways to Capitalize First Letter in Excel

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:

Dataset under analysis

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:

Writing formula for PROPER function

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.

Occupations column capitalized

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:

  1. UPPER Function: The UPPER function converts a text string to all uppercase letters.

  2. LOWER Function: The LOWER function converts a text string to all lowercase letters.

  3. LEFT Function: The LEFT function returns the first character (or characters) in a text string, based on the number of characters you specify.

  4. 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:

Writing formula for the function

Click enter, then drag the fill handle down for each cell to apply the formula to the rest of the column.

Cities column capitalized

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.

Entering a Manual Example

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.

Navigating to Flash Fill

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.

Names column capitalized using Flash Fill

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.

Navigating to transform options

Your data will now open in the Power Query Editor.

Data opens in 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.

Capitalizing 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.

Each word capitalized of Occupations column

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.

Navigating to Custom Column tab

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.

Writing custom column formula

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.

City names capitalized using Power Query

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.

Opening 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.

Inserting a New Module

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 Sub

This macro will capitalize the first letter of each cell in the selected range.

In VBA, the code will look like the following:

Writing VBA code

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.

Running the Macro

This will capitalize the first letter of each cell in the Occupations column.

First letter capitalized using VBA

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.

Data analyst going through an analytics report

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.

Related Posts