Sunday, May 27, 2012

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.


3 comments:

  1. If you are going to use the VBA Editor then this would be quicker and easier:
    MsgBox ActiveCell.Column

    ReplyDelete
  2. For those who are regularly programming VBA code, you can use the Immediate window located at the bottom of the VBA Editor by typing this:
    ?ActiveCell.Column

    Once you type this string, press Enter. The Immediate window will return the corresponding number for your Column.

    It will look like this:
    ?ActiveCell.Column
    731

    ReplyDelete
  3. colNumber = Range(colLetters & "1").Column

    ReplyDelete