Capitalizing the first letter in Excel is incredibly simple, and you have three tools at your disposal to accomplish this task
- You can use the PROPER function
This function will automatically convert a string to capitalize each word
- Use Power Query
Power Query can easily do this transformation by a simple right-click
- Create a macro
Or, you can also to this action inside a Macro
Capitalize the first letter by formula
To capitalize the first letter of each word in your cells by formula, just write the PROPER function
=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).
Camel Case with Power Query
When constructing a query to modify your data using Power Query, you can promptly implement the camel case formatting to your column.
- Select one or more columns
- Right-click in the header of the columns
- Go to Transform
- Select Capitalize each word
- This technique is superior to the PROPER function since you won't need to convert the result into a value.
- The transformation replaces the previous contents of the column seamlessly.
Capitalize the first letter with VBA
In VBA, you can transform your string using the StrConv instruction with the vbProperCase option as follows.
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
19/11/2021 @ 04:13
I would suggest not using the StrConv's vbProper method, rather, use WorksheetFunction.Proper instead.. Here are the two methods acting on the same text string... the Worksheet function method appears to be more robust (compare the output inside the parentheses and you will see what I mean).
MsgBox StrConv("Here is (one-type of) problem.", vbProperCase)
MsgBox WorksheetFunction.Proper("Here is (one-type of) problem.")