How to get Column Number(or index) from Excel Column Letter

character, excel, function

Solution

In VBA:

Public Function GetCol(c As String) As Long
    Dim i As Long, t As Long
    c = UCase(c)
    For i = Len(c) To 1 Step -1
        t = t + ((Asc(Mid(c, i, 1)) - 64) * (26 ^ (Len(c) - i)))
    Next i
    GetCol = t
End Function

Problem

I have searched through this site and googled for a formula. I need to calculate an Excel column number FROM the letter such as: ``` A=1 B=2 .. AA=27 AZ=52 ... AAA=703 ``` The code seems to be 1 digit off after random cycles of the alphabet(AZ -> BA == off digit). It also will seemingly randomly produce the same integer from two different inputs: ``` GetColumnNumber(xlLetter : Text) : Integer //Start of function StringLength := STRLEN(xlLetter); FOR i := 1 TO StringLength DO BEGIN Letter := xlLetter[i]; IF i>1 THEN Count += ((xlLetter[i-1]-64) * (i-1) * 26) - 1; Count += (Letter - 64); END; EXIT(Count); //return value ``` My code example is written in C/AL which is used for Dynamics NAV, but I can write C# or vb.net as well so I wouldn't mind if an example was in either of those languages.

Original source