How to Capitalize First Letter in Excel

During Excel data entries, you may have Proper nouns like persons, companies, months, and places names to record. In such cases, you would have to capitalize the first letter of the word. 

For smaller datasets, you could use the CAPSLOCK key to capitalize the initial letter. But, if you already have the texts in lowercase on your sheet, it can be a little tricky to convert them. This is because Excel hasn’t included the Change Case option in its ribbon like in Microsoft Word.

However, there are a few reliable workarounds you could use to change the lowercase to capital. Depending on the type of text case you want, use any one from Excel’s PROPER function, Flash-fill feature, VBA tool, etc.

Using Flash-Fill

I believe Flash-fill is every Excel user’s favorite as it does nearly all the work for you. When Flash-fills recognizes a certain pattern while performing the tasks in your sheet, it will automatically apply the same effect to the other cells. I suggest you use the Flash-fill if you have a smaller dataset with one or few words to capitalize the first letter. 

Suppose, you have a list of country names in column A with all lowercase. In column B, enter France with the capital F. Then, press Ctrl + E shortcut key to fill the other cells. You’ll have the first letter capital text in the entire data now. 

Using PROPER Function

Even though Excel hasn’t included the Change Case menu in its ribbon, there is an in-built function for it. You could use this function if you have long sentences in your spreadsheet.

The PROPER function returns every word with an initial capital letter, including the articles like the, is, an, etc.

Syntax: PROPER(text)

The PROPER function only takes the text as the function arguments. When you directly insert the word in the formula, you must place them inside the double quotation mark. For Instance =PROPER(“excel”) to return Excel.

However, you do not have to do this if you’re taking a cell reference as a text argument. 

Now, let us capitalize the first letter of the given sentence with the PROPER function. Here, we will insert the cell reference such that we can use an auto-fill handle for the rest of the sentences. For this, we entered the formula as

=PROPER(A1)

Using Formula

With the PROPER function, we got all the initial letters of the word in a capital case. But, if you’re looking to capitalize only the starting word, this method is for you. Here, we will nest UPPER, LEFT, RIGHT, and LEN functions. Let’s learn about each function’s syntax and description first.

Now, we will nest all the functions together and form a formula to capitalize only the initial letter of the word. For this, we will use the formula mentioned in the box.

=UPPER(LEFT(A10,1))&RIGHT(A10,LEN(A10)-1)

Here’s how the formula works:

• UPPER(LEFT(A10,1)): Firstly, the LEFT function returns 1 character from the left of the sentence which is w. Then, the UPPER function capitalizes the result and returns W. 
• RIGHT(A10, LEN(A10)-1): Here, the LEN function returns the total number of characters from cell A10 .i.e 91. Then, the RIGHT function will return 91 -1 = 90 characters from the right of the text string. In short, it returns “hen you enter keys, you do not have to press and hold them as you’d do for other shortcuts”
• UPPER(LEFT(A10,1))&RIGHT(A10,LEN(A10)-1): Finally, the Ampersand (&) symbol joins both UPPER and RIGHT texts. Thus, you will have “When you enter keys, you do not have to press and hold them as you’d do for other shortcuts” as a final output.

Using VBA Tool

If using the nested formulas seems complicated for you, we also have another alternative method. You could use the VBA (Visual Basics for Applications) code to capitalize only the first letter in a word/sentence as we did above.

1. Firstly, select the cell or ranges to convert the initial letter of the word.
2. Right-click on your Sheet Name and choose View Code.
   
3. Now, copy the code given in the box and paste it into the VBA tool.
   

Sub CapitalizeFirstLetter()
Dim Sel As Range
Set Sel = Selection
For Each cell In Sel
    cell.Value = UCase(Left(cell.Value, 1)) & Right(cell.Value, Len(cell.Value) - 1)
Next cell
End Sub

4. Press F5. If prompted, pick Run.
   
5. Close VBA window.