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.
Also Read: Excel VBA - Merge Cells - Unmerge Range
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.Excel Formula - Convert Column Letter to NumberVisual Basic12=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.Excel VBa Convert Column Letter to NumberVisual Basic123456789Function Col_Letter_To_Number(ColumnLetter As String) As DoubleDim cNum As Double'Get Column Number from AlphabetcNum = Range(ColumnLetter & "1").Column'Return Column NumberCol_Letter_To_Number = cNumEnd 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.
Previous Post: Excel VBA – Convert Column Number to Letter & Vice Versa
Next Post: Excel VBA – Merge Cells – Unmerge Range
Excel VBA – Convert Column Letter to Number