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: I think you need to get rid of the +1. ?ColNum(“a”) = 2.

7. 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

8. Try this

   Also from Chip I believe

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

   Temp = ColumnNumber(“D”) ‘ returns 4

9. 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

10. Otra variante:

    Function colnum(x)
    colnum = Range(x & “:” & x).Column
    End Function

---

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