Capitalize the first letter in Excel (Camel Case)

Capitalize the first letter in Excel (Camel Case)
Last Updated on 18/04/2024
Reading time: 2 minutes

Capitalizing the first letter in Excel is incredibly simple, and you have three tools at your disposal to accomplish this task

  1. You can use the PROPER function

    This function will automatically convert a string to capitalize each word

  2. Use Power Query

    Power Query can easily do this transformation by a simple right-click

  3. Create a macro

    Or, you can also to this action inside a Macro

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 in value

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.

  1. Select one or more columns
  2. Right-click in the header of the columns
  3. Go to Transform
  4. Select Capitalize each word
Capitalize the first letter with Power Query
  • 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

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 (Camel Case)

Reading time: 2 minutes
Last Updated on 18/04/2024

Capitalizing the first letter in Excel is incredibly simple, and you have three tools at your disposal to accomplish this task

  1. You can use the PROPER function

    This function will automatically convert a string to capitalize each word

  2. Use Power Query

    Power Query can easily do this transformation by a simple right-click

  3. Create a macro

    Or, you can also to this action inside a Macro

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 in value

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.

  1. Select one or more columns
  2. Right-click in the header of the columns
  3. Go to Transform
  4. Select Capitalize each word
Capitalize the first letter with Power Query
  • 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

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 *