Capitalize first letter in Excel cells

Three ways to convert the first letter of Excel cell

When processing text data in Excel, a common requirement is to capitalize the first letter of a cell. Whether it is a name, product name, or task list, you may encounter some (or even all) cases where letters are inconsistent.

Our previous article discussed the PROPER function, but it capitalizes the initial letter of each word in the cell and the other letters lowercase, so not all cases apply.

Let's see what other options are available through an example of my favorite villain list.

• Use formula to capitalize the initial letter
  • The first letter is capitalized, the remaining letters are lowercase
  • Capitalize the initial letter, ignore the remaining letters
• Using the text toolbox: "Change case"

Use formula to capitalize the initial letter

Excel has many useful functions to capitalize the initial letter of a cell. However, you cannot include both data and formulas that refer to it in a cell. Therefore, you need to create auxiliary columns in the worksheet to place the formula. Once done, after the calculation is complete, you can replace the formula with the value of the formula. Let's start?

The first letter is capitalized, the remaining letters are lowercase

To capitalize only the first letter in an Excel cell while lowering the rest, first insert an additional column to display the results. In my example it is column B. Right-click the column name ( B ), and select Insert from the context menu. The column will be inserted between column A and column C, and if the column header exists, you can change its name:

Place the cursor in a new B2 cell and enter the following formula in it:

=REPLACE(LOWER(C2),1,1,UPPER(LEFT(C2,1)))

Tip: The rest of the lines will most likely automatically fill the adjusted formula. Otherwise, you can quickly copy the formula downward by dragging or dropping or double-clicking the small square in the bottom right corner of the cell with the formula.

Let me explain the meaning of the above formula:

• UPPER(LEFT(C2,1)) converts the first letter of the C2 cell to uppercase.
• The REPLACE function is used to ensure that the entire text is returned and to change a specified letter—in this case the first letter.
• With LOWER(C2) as the first parameter of the REPLACE function, all other letters can be lowercased:

So you will get cells that look like sentences.

Capitalize the initial letter, ignore the remaining letters

To capitalize the first letter of the cell and leave the other characters as is, we will use the same formula as above, but with a slight modification.

But first, make sure to create another column to use the formula. Then, enter the following in B2:

=REPLACE(C2,1,1,UPPER(LEFT(C2,1)))

Note that we removed the "LOWER" section from the beginning of the formula. This small change does not lowercase all letters in the cell, but will still capitalize the first letter:

Tip: If Excel does not automatically do this, don't forget to copy the formula downward.

Using the text toolbox: "Change case"

If you decide you need a faster way to capitalize the initials in Excel cells, you will make an informed choice!

Change case in our text toolbox will check these beautiful letters for you. It is included in Excel's 70+ tool collections—Ultimate Suite:

1. Download and install the Ultimate Suite collection on your PC.

2. Run Excel and click the Change Case Tool icon in the text group under the Ablebits Data tab:

   The add-in pane will appear to the left of the Excel window.

3. Manually select the range of cells to change case, in this case B2:B10. Tip: You can select ranges before running the tool. It will automatically display the selected range in the corresponding fields.

4. Select the sentence case option to make the first letter of each cell capitalize:

   Note: If you want to save a copy of your data just in case, uncheck the Backup Worksheet option before making any changes.

5. Click the Change Case button to view the results:

Note: When each word in a cell (except the first word) begins with a capital letter, the add-in will not only capitalize the first character, but also lowercase the rest.

As you can see, capitalizing letters in Excel is not difficult. Now you can do this with a few mouse clicks and enjoy the results. Welcome to leave a message and ask questions below:)

The above is the detailed content of Capitalize first letter in Excel cells. For more information, please follow other related articles on the PHP Chinese website!