Harald posted a comment that we should have the reverse function, that is, to get the column number from a alpha input. I agree.

Function ColNum(Byval ColumnLetter as string) as Long

‘anyone ?

Skip to content
# Daily Dose of Excel

## Haphazardly Posted Excel Information and Other Stuff

# Column Letters To Numbers

##
10 thoughts on “Column Letters To Numbers”

### Leave a Reply

Harald posted a comment that we should have the reverse function, that is, to get the column number from a alpha input. I agree.

Function ColNum(Byval ColumnLetter as string) as Long

‘anyone ?

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

How about,

Function ColNum(ByVal ColumnLetter As String) As Long

On Error Resume Next

ColNum = Range(ColumnLetter & “1?).Column

End Function

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

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

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.

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

David: I think you need to get rid of the +1. ?ColNum(“a”) = 2.

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

Try this

Also from Chip I believe

Function ColumnNumber(ColLetter) As Integer

ColumnNumber = Cells(1, ColLetter).Column

End Function

Temp = ColumnNumber(“D”) ‘ returns 4

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

Otra variante:

Function colnum(x)

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

End Function