Text functions

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
Data to capitalize

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])

Capitalize first letter with formula

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)

Paste Special 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
Capitalize the first letter with Power Query

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

Related posts

Function SEARCH

Frédéric LE GUEN

Split Text by delimitor

Frédéric LE GUEN

Convert roman numerals with Excel

Frédéric LE GUEN

Leave a Comment