Convert an excel or spreadsheet column letter to its number in Pythonic fashion
excel, google-sheets, python, python-2.x
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])
Problem
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#