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.
- Select one or more columns
- Right-click in the header of the columns
- Go to Transform
- 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