Question

Programming Riddle: How might you translate an Excel column name to a number?

I was recently asked in a job interview to resolve a programming puzzle that I thought it would be interesting to share. It's about translating Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.

You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number.

The solution can be in any language.

 45  25237  45
1 Jan 1970

Solution

 36

Sounds like a standard reduce to me:

Python:

def excel2num(x): 
    return reduce(lambda s,a:s*26+ord(a)-ord('A')+1, x, 0)

C#:

int ExcelToNumber(string x) {
    return x.Aggregate(0, (s, c) => s * 26 + c - 'A' + 1 );
}
2009-06-17

Solution

 16

I wrote this ages ago for some Python script:

def index_to_int(index):
    s = 0
    pow = 1
    for letter in index[::-1]:
        d = int(letter,36) - 9
        s += pow * d
        pow *= 26
    # excel starts column numeration from 1
    return s
2009-04-18