Capitalize the first letter in Excel

Capitalize the first letter in Excel
Last Updated on 19/08/2022
Reading time: 2 minutes

Capitalize the first letter in Excel is very easy and you have 3 tools 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, just write the PROPER function

=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 quickly 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

1 Comment

  1. Rick Rothstein
    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.")

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Capitalize the first letter in Excel

Reading time: 2 minutes
Last Updated on 19/08/2022

Capitalize the first letter in Excel is very easy and you have 3 tools 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, just write the PROPER function

=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 quickly 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

1 Comment

  1. Rick Rothstein
    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.")

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *