Capitalize the first letter in Excel

Reading Time: 2 minutes

Capitalize the first letter in Excel is very easy and you have 3 techniques to do that

• By formula
• With Power Query
• With VBA

Capitalize the first letter by formula

To capitalize the first letter of each word in your cells by formula, you just have to use the function PROPER with the contained of the cells as argument.

=PROPER(B2) or =PROPER([@columnName])

But the job isn't finished.

You must also transform the result of the formula into values with the tool copy / paste special (option values)

Capitalize the first letter with Power Query

If you build a query to manipulate your data with Power Query, you can easily capitalize the first letter of a column.

1. Select one or more columns
2. Right-click in the header of the columns
3. Go to Transform
4. Capitalize each word

This technique is much better because you don't duplicate the contain of your column. The transformation remplaces the previous contain of the column.

Capitalize the first letter with VBA

In VBA, the code to transform your string is the following possible with the instruction StrConv and the option vbProperCase

Sub Capitalize_First_Letter()
Dim MyText As String
Dim i As Long
    For i = 2 To 11
        Cells(i, 2) = StrConv(Cells(i, 2), vbProperCase)
    Next
End Sub