Excel VBA – Convert Column Letter to Number

Column Letter to Number

For each cell in worksheet the address will have column Letter and then Row number.

For example: A1, B1, C1, etc.,

There is another representation of cells like: A1 as Cells(1,1), B1 as Cells (1,2) , C1 as cells (1,3) and so on.

To convert from one representation to another, we need a way to convert the column letter to its corresponding number.

Note: To Get Column Letter from Number refer this article.

Unfortunately Excel does not have a built in formula to do this conversion. You can use the 2 methods explained in this article.

Excel Formula to Get Column Number from letter

Assume we need to convert column letter ‘AA’ to its number (27).

Use this formula in any of the Excel sheets & press ‘Enter’ key.

=COLUMN(AA1) Or
=COLUMN(INDIRECT("AA1"))

Both the above functions will return value 27. i.e., the Column number for Column AA.

In both these function don’t forget to append the row number. Though it is insignificant, if not used, the functions will give a #REF error.

Now, lets see how to get Column alphabet letter through Vba code.

Excel VBA – To Convert Column Letter to Number

Assume same example as above. We are going to get value 24 – column number for the letter ‘AA’.

Use this vba code.

Function Col_Letter_To_Number(ColumnLetter As String) As Double
    Dim cNum As Double
        
    'Get Column Number from Alphabet
    cNum = Range(ColumnLetter & "1").Column
    
    'Return Column Number
    Col_Letter_To_Number = cNum
End Function

In Excel sheet, type ‘=Col_Letter_To_Number(“AA”)’ & press Enter key.

The function will return 27 as its column number. Same way, this function can also be used in any vba macro function.