Excel and Excel VBA - Convert column letter to number

The columns of the excel sheet are labeled as ‘A’, ‘B,’ ‘C’…. It looks straightforward but it gives the difficult when I did VBA programming.

What is the corresponding number to the column K?

1) Count by figure.

2) In Cell A1, type in =1

             In Cell B1, type in  “=A1+1”
             Then, drag the formula from Cell B1 to Cell K1

3) In Cell A1, type in  =CODE("K")-64

The second question, what is the corresponding number to the column BK?

1) Count by figure. (I do not think it is a good idea)

2) In Cell A1, type in =1
             In Cell B1, type in  “=A1+1”
             Then, drag the formula from Cell B1 to Cell BK1 (It is not a good idea neither)

3) In Cell A1, type in  =(CODE("B")-64 )*26+(CODE("K")-64 )

 Therefore, you can see the method 1 and 2 are not bad for the first couple of excel columns but it is not a good idea when number of columns is increased.

Method 3 is a good idea.

The method 4 is using VBA code. I prefer this.

Sub ColumnLetterToNumber()

Dim InputLetter As String
Dim OutputNumber As Integer
Dim Leng As Integer
Dim i As Integer

InputLetter = InputBox("The Converting letter?")  ' Input the Column Letter
Leng = Len(InputLetter)
OutputNumber = 0

For i = 1 To Leng

   OutputNumber = (Asc(UCase(Mid(InputLetter, i, 1))) - 64) + OutputNumber * 26
Next i

MsgBox OutputNumber   'Output the corresponding number
 

End Sub

Using the above code, I can quickly find the number I need. And both lower case and upper case letter give the same result.

For example,

Input letter = “abc”

Output number = 731

                                                                                        © Copyright Exceltipsandkeys All Rights Reserved.