Converting column letter to number

Question

I found code to convert number to column letter.

How can I convert from column letter to number?

Sub colLtr()
Dim mycolumn
mycolumn = 1000
Mcl = Left(Cells(1, mycolumn).Address(1, 0), InStr(1, Cells(1, mycolumn).Address(1, 0), "$") - 1)
MsgBox Mcl
End Sub

Answer 1

You can reference columns by their letter like this:

Columns("A")

So to get the column number, just modify the above code like this:

Columns("A").Column

The above line returns an integer (1 in this case).

So if you were using the variable mycolumn to store and reference column numbers, you could set the value this way:

mycolumn = Sheets("Sheet1").Columns("A").Column

And then you could reference your variable this way:

Sheets("Sheet1").Columns(mycolumn)

or to reference a cell (A1):

Sheets("Sheet1").Cells(1,mycolumn)

or to reference a range of cells (A1:A10)you could use:

Sheets("Sheet1").Range(Cells(1,mycolumn),Cells(10,mycolumn))

Answer 2

The answer given may be simple but it is massively sub-optimal, because it requires getting a Range and querying a property. An optimal solution would be as follows:

Function getColIndex(sColRef As String) As Long
  Dim sum As Long, iRefLen As Long
  sum = 0: iRefLen = Len(sColRef)
  For i = iRefLen To 1 Step -1
    sum = sum + Base26(Mid(sColRef, i)) * 26 ^ (iRefLen - i)
  Next
  getColIndex = sum
End Function

Private Function Base26(sLetter As String) As Long
  Base26 = Asc(UCase(sLetter)) - 64
End Function

Some examples:

getColIndex("A")   '-->1
getColIndex("Z")   '-->26
getColIndex("AA")  '-->27
getColIndex("AZ")  '-->52
getColIndex("AAA") '-->703

Answer 3

To see the numerical equivalent of a letter-designated column:

Sub dural()
    ltrs = "ABC"
    MsgBox Cells(1, ltrs).Column
End Sub

Answer 4

My Comments

ARich gives a good solution and shows the method I used for a while but Sancarn is right, its not optimal. It's a little slower, will cause errors if the wrong input is given, and is not very robust. Sancarn is on the right track, but lacks a little error checking: for example, getColIndex("_") and getColIndex("AE"), will both return 31. Other non-letter characters (ex: "*") sometimes return various negative values.

Working Function

Here is a function I wrote that will convert a column letter into a number. If the input is not a column on the worksheet, it will return -1 (unless AllowOverflow is set to TRUE).

Function ColLetter2Num(ColumnLetter As String, Optional AllowOverflow As Boolean) As Double
'Converts a column letter to a number (ex: C to 3, A to 1, etc). Returns -1 if its invalid.
' @ColumnLetter - the letter(s) to convert to a number.
' @AllowOverflow - if TRUE, can return a number greater than the max columns.
    On Error GoTo invalidCol
    If Len(ColumnLetter) = 0 Then GoTo invalidCol
    Dim thisChar As String
    For i = 1 To Len(ColumnLetter) 'for each character in input
        thisChar = Mid(ColumnLetter, i, 1) 'get next character
        If Asc(UCase(thisChar)) >= 65 And Asc(UCase(thisChar)) <= 90 Then 'if the character is a letter
            ColLetter2Num = ColLetter2Num + (26 ^ (Len(ColumnLetter) - i)) * (Asc(UCase(thisChar)) - 64) 'add its value to the return
        Else
            GoTo invalidCol 'if the character is not a letter, return an error
        End If
        If AllowOverflow = False And (ColLetter2Num = 0 Or ColLetter2Num > Columns.Count) Then
        'if the value is not inside the bounds of the sheet, return an error and stop
invalidCol:
            ColLetter2Num = -1 'error
            Exit Function 'stop checking
        End If
    Next i
End Function

Examples

 Sub test()
        Debug.Print ColLetter2Num("A") 'returns 1
        Debug.Print ColLetter2Num("IV") 'returns 256        (max columns for excel 2003 and prior))
        Debug.Print ColLetter2Num("XFD") 'returns -1        (invalid because IV is the last column for .xls workbooks)
        Debug.Print ColLetter2Num("XFD", True) 'returns 16384 (does not return -1 because AllowOverflow = TRUE)
        Debug.Print ColLetter2Num("A_", True) 'returns -1   (invalid because "_" is not a column)
        Debug.Print ColLetter2Num("132", True) 'returns -1  (invalid because "1" is not a column)
        
        If ColLetter2Num("A") <> -1 Then
            Debug.Print "The input is a valid column on the sheet."
        Else
            Debug.Print "The input is NOT a valid column on the sheet."
        End If
    End Sub

Answer 5

If needed to write a robust function, just be carefull not to use worksheet properties (like @Zac's below) as it it will crash if active sheet is not a Worksheet, eg. a Chart