Convert Letter base 26 system to Number base 10 system

[JTBorton]

So I have noticed a lot of people in previous posts and in other forums asking the question of how to convert a letter base 26 number system to a decimal base 10 system. Seeing as I just had to deal with it and figure it out, I thought I'd share it with anyone who may need it. Here is one method of doing it, it can certainly be improved:

Function GetColumnNumber(ColumnLetter As String)
Dim Ltr As String
Dim Catch As String
Dim F As Integer
Dim J As Integer
[COLOR=green]
'For strings longer than 1 letter, this function follows the following methodology:
'Lets use the Base ten system as reference, up to the millions place:
'10^6 | 10^5 | 10^4 | 10^3 | 10^2 | 10^1 | 10^0
'Such that 7,283,749 is equal to:
' 7*(10^6) + 2*(10^5) + 8*(10^4) + 3*(10^3) + 7*(10^2) + 4*(10^1) + 9*(10^0) = 7,283,749
'Therefor we can convert letters to numbers using the same method:
'26^6 | 26^5 | 26^4 | 26^3 | 26^2 | 26^1 | 26^0
'So, to convert CGDF to the decimal system, we first convert each letter using ASCII
'C = Asc("C") - 64 = 3, G = Asc("G") - 64 = 7, D = ... = 4, F = ... = 6
'Then, CGDF is equal to:
'3*(26^3) + 7*(26^2) + 4*(26^1) + 6*(26^0) = 57,570
'Or, DecimalEquivalent = The Summation of [from n to J]
'                        [ Asc(Ltr(n)) - 64 ] * [ 26 * (26 ^ (J-n))]
'                        Where J is the total length of the string
'                        Ltr is the letter to be evaluated
'                        n is the position of the letter in the string reading from left to right
[/color]

ColumnLetter = UCase(ColumnLetter)
If Not IsNumeric(ColumnLetter) Then
Start_Proc:
    If Len(ColumnLetter) < 2 Then
        ' Columns A-Z
        GetColumnNumber = Asc(ColumnLetter) - 64
        'GetColumnLetter = Chr(ColumnNumber + 64)
    Else
        J = Len(ColumnLetter)
        For N = 1 To Len(ColumnLetter)
            Ltr = Mid(ColumnLetter, N, 1)
            Catch = Catch & Ltr
            If Not IsNumeric(Ltr) Then
                GetColumnNumber = GetColumnNumber + ((Asc(Ltr) - 64) * (26 ^ (J - N)))
            Else
                [COLOR=green]'Guard against numbers mixed in.  For this application, I want to _
                 work only with the letters, so I'll just pull them out and start over[/color]
                ColumnLetter = Catch
                GoTo Start_Proc
            End If
        Next N
    End If
Else
    GetColumnNumber = CInt(ColumnLetter)
    Exit Function
End If

End Function

Here I am using it to get the column number by passing the column letter. Hope this is useful to someone.

The same algorithm can be followed to convert between any number system, except instead of [Asc(Ltr) - 64] you would have to modify how each individual parameter is converted to base 10.

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]

 

[JTBorton]

Oops, had a little mistype there,

Change: [ Asc(Ltr) - 64 ] * [ 26 * (26 ^ (J-n))]
To: [ Asc(Ltr) - 64 ] * [ 26 ^ (J-n)]

-JTBorton
Well, You can try banging your head against the wall, but you just end up with lost-time injuries and damaged equipment. [M. Passman]