pythonexcelgoogle-sheetspython-2.x

Convert an excel or spreadsheet column letter to its number in Pythonic fashion


Is there a more pythonic way of converting excel-style columns to numbers (starting with 1)?

Working code up to two letters:

def column_to_number(c):
    """Return number corresponding to excel-style column."""
    number=-25
    for l in c:
        if not l in string.ascii_letters:
            return False
        number+=ord(l.upper())-64+25
    return number

Code runs:

>>> column_to_number('2')
False
>>> column_to_number('A')
1
>>> column_to_number('AB')
28

Three letters not working.

>>> column_to_number('ABA')
54
>>> column_to_number('AAB')
54

Reference: question answered in C#


Solution

  • There is a way to make it more pythonic (works with three or more letters and uses less magic numbers):

    def col2num(col):
        num = 0
        for c in col:
            if c in string.ascii_letters:
                num = num * 26 + (ord(c.upper()) - ord('A')) + 1
        return num
    

    And as a one-liner using reduce (does not check input and is less readable so I don't recommend it):

    col2num = lambda col: reduce(lambda x, y: x*26 + y, [ord(c.upper()) - ord('A') + 1 for c in col])