Welcome, guest | Sign In | My Account | Store | Cart

A post-it suggestion on how to convert "Excel style" notation of columns to a number.

Python, 36 lines
 ``` 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36``` ```def letter2num(letters, zbase=False): """A = 1, C = 3 and so on. Convert spreadsheet style column enumeration to a number. Answers: A = 1, Z = 26, AA = 27, AZ = 52, ZZ = 702, AMJ = 1024 >>> letter2num('A') == 1 True >>> letter2num('Z') == 26 True >>> letter2num('AZ') == 52 True >>> letter2num('ZZ') == 702 True >>> letter2num('AMJ') == 1024 True >>> letter2num('AMJ', zbase=True) == 1023 True >>> letter2num('A', zbase=True) == 0 True """ letters = letters.upper() res = 0 weight = len(letters) - 1 for i, c in enumerate(letters): res += (ord(c) - 64) * 26**(weight - i) if not zbase: return res return res - 1 if __name__ == '__main__': import doctest doctest.testmod() ```

The "need" for such a function could occur when working with tools to interact with spreadsheets.

#### 1 comment Martin Miller 8 years, 7 months ago

Here's the inverse function:

``````def excel_style(row, col):
""" Convert given row and column number to an Excel-style cell name. """
quot, rem = divmod(col-1, 26)
return((chr(quot-1 + ord('A')) if quot else '') +
(chr(rem + ord('A')) + str(row)))

if __name__ == '__main__':
addresses = [(1,   1), (1,  26),
(1,  27), (1,  52),
(1,  53), (1,  78),
(1,  79), (1, 104)]

print '(row, col) --> Excel'
print '---------------------'
print '({:3d}, {:3d}) --> {!r}'.format(row, col, excel_style(row, col))

#Output:
#
#<!-- language: lang-none -->
#
#    (row, col) --> Excel
#    ---------------------
#    (  1,   1) --> 'A1'
#    (  1,  26) --> 'Z1'
#    (  1,  27) --> 'AA1'
#    (  1,  52) --> 'AZ1'
#    (  1,  53) --> 'BA1'
#    (  1,  78) --> 'BZ1'
#    (  1,  79) --> 'CA1'
#    (  1, 104) --> 'CZ1'
`````` Created by Tomas Nordin on Sat, 20 Sep 2014 (MIT)

### Required Modules

• (none specified)