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.