VBA: How to Convert Column Number to Letter


You can use the following syntax in VBA to convert a column number to a letter:

Sub ConvertNumberToLetter()
Range("B2") = Split((Columns(Range("A2")).Address(, 0)), ":")(0)
End Sub

This particular macro will convert the column number in cell A2 to a letter and display the letter in cell B2.

For example, if the value in cell A2 is 4 then cell B2 will display “D” since this is the letter that corresponds to the fourth column in Excel.

The following example shows how to use this syntax in practice.

Example: Convert Column Number to Letter in VBA

Suppose we would like to know what column letter corresponds to a column number of 4 in Excel:

We can create the following macro to convert this number to a letter:

Sub ConvertNumberToLetter()
Range("B2") = Split((Columns(Range("A2")).Address(, 0)), ":")(0)
End Sub

When we run this macro, we receive the following output:

VBA convert column number to letter

Cell B2 displays a value of “D” since this is the letter that corresponds to the fourth column.

If we change the number in cell A2 and run the macro again, then cell B2 will display the updated column letter.

For example, suppose we change the number in cell A2 to 73 and run the macro again:

Cell B2 now displays a value of “BU” since this is the column letter that corresponds to the 73rd column.

Additional Resources

The following tutorials explain how to perform other common tasks in VBA:

VBA: How to Find Last Used Row
VBA: How to Find Last Used Column
VBA: How to Count Number of Used Columns

Featured Posts

One Reply to “VBA: How to Convert Column Number to Letter”

  1. This works great for me except when the column number is 36. It works for 35, 37, 28, 110 but not 36. Any thoughts?
    Thanks

Leave a Reply

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