How To Capitalize First Letter of a Text String or Each WordFormulas, Examples and More!

Written By:
Lisa Borga

For many Excel users, it is common to assume that worksheets are for storing numerical data, but the fact is nothing could be further from the truth.

Excel is an incredibly popular software for storing, organizing, and manipulating text data as well as numbers.

Whether it is a list of names, product inventory, designing documents, or anything else, Excel provides a strong tool for storing and using text and getting the job done.

One of the most common tasks many users may come across when it comes to inputting text into worksheets is capitalizing the first letter in each cell.

From the name of each product or employee, dates, tasks, and many other data points, it is typical to capitalize the first letter of each word or the first word in each cell.

When data is inputted from another source or for some other reason, it is not capitalized. If this happens, it is often necessary to go back and re-capitalize each word.

Though it may be simple to understand the job, it can be extremely cumbersome to do this to dozens or even thousands of data points.

Fortunately, Excel provides a few easier ways to do this that don’t require you to wear out your “Shift” key.

Here we will show you how to capitalize the first letter of a text string or each word with a few different techniques.

So, without any further delay, let’s get started!

Using Formulas to Capitalize the First Letter

As with most tasks in Excel, there are formulas that can be used to capitalize the first letter of each word. However, your specific needs may not always be the same.

There are two basic scenarios for how you may want to capitalize the first letter in a text string or cell.

This includes capitalizing the first letter of each word or capitalizing only the first letter of the first word in a cell.

Given that both of these scenarios are quite frequent to come across, we will take a look at formulas to handle both of these scenarios.

How To Capitalize the First Letter Using the PROPER Function

With the PROPER function, it is easy to capitalize the first letter of each word.

This function is, in fact, designed to do just this job. Consider a data set like this one which contains titles in lowercase that we would like to capitalize.

With the PROPER function, we can capitalize the first letter of each word quickly. Simply follow these steps to capitalize the first letter of each word.

  1. Choose a column where you would like the results to be located. This is necessary because the original data and the formula you will use cannot be located in the same cell.
  2. Now enter the formula =PROPER(B2:B7), substituting your own cell references into the range.
  3. Run the formula, and the first letter of every word should appear capitalized in your chosen cells.

As you can see, this is an extremely simple way to capitalize the first letter of each word.

Now that you have the results, you can easily copy the cells containing them and use the “Paste” drop-down on the ribbon to paste them as values.

This will prevent simply copying and pasting the formula and allow you to use the results as text instead.

Capitalizing the First Letter of the First Word Without Changing Other Text

Unfortunately, the PROPER function cannot be used for capitalizing the first letter of only the first word in a text string or cell.

However, even without a dedicated function, Excel does provide a wide array of functions that can, in combination, still be used to do this.

First, let’s look at a formula that can capitalize the first letter of a text string without affecting any of the other text. The formula we can use is:

=UPPER(LEFT(B2:B7,1))&RIGHT(B2:B7,LEN(B2:B7)-1)

This formula will use the LEFT function in order to extract the initial character from the text in the cell and then the UPPER function to capitalize it.

It will then concatenate and extract the remaining characters using the RIGHT function, leaving them unchanged.

Now simply follow these steps to use this formula.

  1. You cannot place the formula in the same cell as your original data, so choose a blank cell where you would like the result to be placed with open cells in the column below it as necessary.

  1. Simply enter the formula =UPPER(LEFT(B2:B7,1))&RIGHT(B2:B7,LEN(B2:B7)-1) into a blank cell and run it.

  1. Your results should appear with the first word capitalized.

This method is simple to use and powerful. Again you can copy and paste the results as values to separate them from the formula and use them however you like.

Capitalizing the First Letter of a Text String While Changing the Rest to Lowercase

You may also come across situations in which you need to capitalize the first letter of a text string but change the remainder to lowercase. This often arises when you need to change the text to sentence case.

Just the same as when capitalizing the first letter while leaving the rest unchanged, there is no one function that can be used to accomplish this. However, there are still combinations of formulas we can  use, such as:

=REPLACE(LOWER(B2:B7),1,1,UPPER(LEFT(B2:B7,1)))

In this formula, we use the LOWER function to convert the whole text into lower case, and then the UPPER, LEFT, and REPLACE function allows us to convert the first letter of the cell string alone with an upper case character.

Simply enter the formula substituting your own cell references into a blank cell and run it to convert the first letter.

As with the other methods we have outlined, the results will still be connected to the formula.

However, if you would like to simply keep the results, you can copy them and paste them as values using the “Paste” drop-down on the ribbon.

Capitalizing the First Letter of Each Word in a Cell Using VBA Code

VBA, or Visual Basic for Applications, is the coding language built into Excel and most other Office applications.

This makes it a powerful tool for achieving just about anything we could want in Excel, and this includes capitalizing each word in a cell.

For those who are familiar with working in VBA, this can offer a fast and effective tool for capitalizing the first letters of words while offering unmatched flexibility due to the ability to alter the code to match whatever your needs are.

Here we will provide a basic code that can be used to capitalize the first letter of each word. Here is how you can enter and use it.

  1. Navigate to the “Developer” tab and select “Visual Basic” from the “Code” group in the ribbon. If it is not already present on your ribbon, go to “File,” “Options,” and “Customize Ribbon” to add “Developer” to your “Main Tabs.”

  1. In the “VBA Editor,” select “Insert,” and in the drop-down list, “Module.” This will open a window where you can enter the following code:
Sub Capitalize() 

Dim c As Range

Dim xyz As Range

On Error Resume Next

xTitleId = "Capitalize the First Letter"

Set xyz = Application.Selection

Set xyz = Application.InputBox(“Range”, xTitleId, xyz.Address, Type:=8)

For Each c In xyz

c.Value = Application.Proper(c.Value)

Next

End Sub

  1. Run the code, and in the dialog box that appears, enter the range that you would like to capitalize.

  1. Select “OK,” and the first letter of every word in your specified range should now be in upper case.

As you can see, this isn’t so difficult and offers a very flexible tool with a little bit of familiarity with VBA coding.

Conclusion

Now you have seen a few different ways to capitalize the first letter of either every word or only the first word in a text string.

With several different tools under your belt, you are well-equipped to handle just about any situation that comes up and ensure that your text is capitalized however you need.