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

Spread sheet style column enumeration letter to number (Python recipe) by Tomas Nordin
ActiveState Code (http://code.activestate.com/recipes/578941/)

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

Python, 36 lines
Download
 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.

Tags: conversion, excel, libreoffice, python, spreadsheet

1 comment

Martin Miller 9 years, 6 months ago  # | flag

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 '---------------------'
    for row, col in addresses:
        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)
◄ Python recipes (4591) ►
◄ Tomas Nordin's recipes (6) ►

Required Modules

  • (none specified)

Other Information and Tasks