How to convert between positive integers and alphabetic index/counter values

Question

I'm writing code for a system that uses alphabetic indices (well, sub-indices rather), and need a way to convert to and from integers for things like ordering and determining the size of a range. To be clear, by "alphabetic indices" I mean a system like Excel uses for its column labels, i.e. start with A, B, ..., Y, Z, and then go to AA, AB, ..., ZY, ZZ, then on to AAA, AAB, ..., you get the idea. The indices need to start at A = 1, and increment by one. So, Z = 26, AA = 27, etc.

For example, if given the string "ABC" the alpha-to-integer function return value would be equal to the number of alphabetic indices from A to ABC, inclusively. In this case, that's 26 for A-Z, 26*26 for AA-ZZ, and 29 (26+3) for AAA-ABC, for a total of 731. This one is a simple problem of iterating over the string.

Going the other way is harder. Given the integer 731, the integer-to-alpha function would return the 731st alphabetic index, which is "ABC". This requires some funky arithmetic.

Notably, this is not the same as a simple base-10 to base-26 conversion problem or vice versa, because in such a system, A is 0, AA is also 0, and 26 is BA.

After being unable to find a solution for the latter of the two functions, I have come up with an algorithm myself and have posted it as an answer below, for anyone else with a similar need.

EDIT: If you are specifically using this in Excel to convert column names, there are built-in functions for that. This is for general-case alphabetic indexing.

Answer 1

Here's are the fastest and cleanest general algorithms I was able to come up with, in pseudocode since this can apply to any language. This assumes that the character encoding groups the characters A-Z together, in order.

Alpha to Index

Uses Horner's method to minimize the number of operations needed.

uint alphaToIndex(string alpha)

    uint result = 0

    for i = 0; i < alpha.length; i++
        result = result * 26 + alpha[i] - 'A' + 1

    return result

Index to Alpha

This one is a bit more arcane. Here, str() converts an integer to a single-char string, and int() converts a boolean to an integer. This also assumes that the modulus operator % gives strictly positive values, i.e. -1 % 26 == 25.

string indexToAlpha(uint n)

    string result = ""

    while n > 0
        uint remainder = n % 26
        result = str('A' + (remainder - 1) % 26) + result
        n = n / 26 - int(remainder == 0)
    
    return result

Note, if your language has mutable strings, it is faster to append each character onto the end and then flip the string at the end, rather than create a new string with the character prepended onto the current string.

Answer 2

If your numbers are guaranteed to be in the range 1..16384, you can make use of Excel with =TEXTBEFORE(ADDRESS(1,A1,2),"$") where A1 contains the number to be converted.

To convert the alphabet charcter to a number, use: =COLUMN(INDIRECT(A2 & 1))

If your values might be greater than 16,384, then you can try these two VBA routines to convert back and forth:

Option Explicit

Function NumberToLetterSequence(Number As Long) As String
    Dim n As Long
    Dim c As Byte
    Dim s As String

    n = Number
    Do
        c = ((n - 1) Mod 26)
        s = Chr(c + 65) & s
        n = (n - c) \ 26
    Loop While n > 0
    NumberToLetterSequence = s
End Function

Function LetterSequenceToNumber(Letters As String) As Long
    Dim n As Long
    Dim s() As String
    Dim v As Variant
    Dim i As Long
    
ReDim s(1 To Len(Letters))
For i = 1 To Len(Letters)
    s(i) = Mid(Letters, i, 1)
Next i

For i = 1 To UBound(s)
    n = n + (Asc(s(i)) - 64) * 26 ^ (UBound(s) - i)
Next i
    
LetterSequenceToNumber = n

End Function