10 thoughts on “Column Letters To Numbers

  1. How about,

    Function ColNum(ByVal ColumnLetter As String) As Long
    On Error Resume Next
    ColNum = Range(ColumnLetter & “1?).Column
    End Function

  2. Apologies for the formatting, the indents seem to fall out when it gets posted…

    =======================================

    Function ColNum(ByVal ColumnLetter As String) As Long
    ‘Returns the column reference if valid. If reference is
    ‘invalid, returns 0.
    ColNum = 0
    If Len(ColumnLetter) = 2 Then
    If ValidColumn(Left(ColumnLetter, 1), False) And _
    ValidColumn(Right(ColumnLetter, 1), True) Then
    ColNum = ThisWorkbook.Sheets(1).Range(ColumnLetter & “1?).Column
    Else
    ColNum = 0
    End If
    ElseIf Len(ColumnLetter) = 1 Then
    If ValidColumn(Left(ColumnLetter, 1), False) Then
    ColNum = ThisWorkbook.Sheets(1).Range(ColumnLetter & “1?).Column
    Else
    ColNum = 0
    End If
    End If
    End Function

    Function ValidColumn(TestChar As String, IVTest As Boolean) As Boolean
    ValidColumn = False
    If IVTest Then
    If Asc(UCase(TestChar)) >= 65 And Asc(UCase(TestChar)) < = 86 Then
    ValidColumn = True
    End If
    Else
    If Asc(UCase(TestChar)) >= 65 And Asc(UCase(TestChar)) <= 90 Then
    ValidColumn = True
    End If
    End If
    End Function

  3. This function will work even if Microsoft expands the columns to allow three or more letters:

    Function ColNum(ByVal ColumnLetter As String) As Long
    Dim LenColLetter As Integer, i As Integer
    ColNum = 0: LenColLetter = Len(ColumnLetter)
    For i = 1 To LenColLetter
    ColNum = ColNum + (26 * (LenColLetter – i) * (Asc(UCase(Mid(ColumnLetter, i, 1))) – 64)) + 1
    Next i
    End Function

  4. Oops, the 5th line should read:

    ColNum = ColNum + (26 ^(LenColLetter – i) * (Asc(UCase(Mid(ColumnLetter, i, 1))) – 64)) + 1

    The difference is power vs multiplication.

  5. David

    THis is good. But I get “2? as a result of “A”. After removing the +1 it said 1. I ran this, combining the two:

    Sub test()
    Dim L As Long, L2 As Long, S As String
    L = 0
    Do
    L = L + 1
    S = ColumnLetter(L)
    L2 = colNum(S)
    If L2 <> L Then
    MsgBox L & ” vs ” & L2, , S
    Exit Sub
    End If
    Loop Until L > 1000000
    MsgBox S, , L
    End Sub

    No error. So either both are great or both are equally wrong. Unfortunately I have only 256 columns to check against :-)

    Best wishes Harald

  6. David,
    this eliminates the extra math. i.e. (26 * (LenColLetter – i)

    Function ColNum(ByVal ColumnLetter As String) As Long
    Dim LenColLetter As Integer, i As Integer
    ColNum = 0: LenColLetter = Len(ColumnLetter)
    For i = 1 To LenColLetter
    ColNum = ColNum * 26 + (Asc(UCase(Mid(ColumnLetter, i, 1))) – 64)
    Next i
    End Function

  7. Try this

    Also from Chip I believe

    Function ColumnNumber(ColLetter) As Integer
    ColumnNumber = Cells(1, ColLetter).Column
    End Function

    Temp = ColumnNumber(“D”) ‘ returns 4

  8. Dick, here’s a function that includes error checking and covers all positive long integers:
    Function ColumnNumber(ByVal strCol As String) As Long
    Dim length As Long, i As Long
    strCol = UCase(strCol)
    length = Len(strCol)
    If length < 1 Or length > 7 Then
    ColumnNumber = -1
    Exit Function
    End If
    If Not strCol Like Replace(Space(length), ” “, “[A-Z]”) Then
    ColumnNumber = -1
    Exit Function
    End If
    If length = 7 Then
    If strCol > “FXSHRXW” Then
    ColumnNumber = -1
    Exit Function
    End If
    End If
    ColumnNumber = 0
    For i = 1 To length
    ColumnNumber = ColumnNumber + (Asc(Mid$(strCol, i, 1)) – 64) * 26 ^ (length – i)
    Next i
    End Function


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published. Required fields are marked *