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