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 =1In 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.
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 LengOutputNumber = (Asc(UCase(Mid(InputLetter, i, 1))) - 64) + OutputNumber * 26
MsgBox OutputNumber 'Output the corresponding number
Using the above code, I can quickly find the number I need. And both lower case and upper case letter give the same result.
Input letter = “abc”
Output number = 731
© Copyright Exceltipsandkeys All Rights Reserved.