CODEWITHSUNDEEP
pythonexcelgoogle-sheetspython-2.x
paragbaxi
paragbaxi

Reputation: 4223

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#

Upvotes: 34

Views: 45053

Answers (17)

Randum
Randum

Reputation: 211

You could just add the following to the console after installing the openpyxl module:

>>> from openpyxl.utils import get_column_letter, column_index_from_string

>>> get_column_letter(1)
'A'
>>> column_index_from_string('A')
1

Just change the letters and number to suit your needs.

Upvotes: 18

Iwan B.
Iwan B.

Reputation: 4166

Concise and elegant Ruby version:

def col_num(col_name)
    col_name.split(//).inject(0) { |n, c| n * 26 + c.upcase.ord - "A".ord + 1 }
end

Upvotes: 1

Callum Herries
Callum Herries

Reputation: 51

Here is a recursive solution:

def column_string_to_num(s):
    n = ord(s[-1]) - 64
    if s[:-1]:
        return 26 * (column_string_to_num(s[:-1])) + n
    else:
        return n
    
column_string_to_num("AB")
#output: 28

The inverse can also be defined recursively, in a similar way:

def column_num_to_string(n):
    n, rem = divmod(n - 1, 26)
    next_char = chr(65 + rem)
    if n:
        return column_string(n) + next_char
    else:
        return next_char

column_num_to_string(28)
#output: 'AB'

Upvotes: 1

Brayoni
Brayoni

Reputation: 766

You could also do it by a series of multiplies and adds as follows. Here "A" will equal to 1. Running time is O(n) where n is the length of the column, col.

import functools
def spreadsheet_column_encoding(col):
    return functools.reduce(
        lambda result, char: result * 26 + ord(char) - ord("A") + 1, col, 0
    )

E.g ZZ = 702:

0 * 26 + 90 - 65 + 1 = 26
26 * 26 + 90 - 65 + 1 = 702

P.S: ord('Z') = 90

To convert number to column letter, kindly see my answer here. You get to do the opposite using division and modulus calculations.

Upvotes: 0

Ji Wei
Ji Wei

Reputation: 881

Use:

LETTERS = list(string.ascii_uppercase)
def column_number(column_id):
    return sum([(LETTERS.index(j)+1)*(26**i) for i,j in enumerate(column_id[::-1])])

There are several parts to this one-liner, so here's the explanation:

column_id[::-1]: reverses the string, e.g. converts 'AZ' to 'ZA', there's a good reason to do so, which we will see in a bit.

enumerate(): produces a iterable, e.g. (0, 'Z'), (1, 'A')

With some observation:

 A -> 1  = (26**0)*1              # ** is the exponential operator
 B -> 2  = (26**0)*2 
 Z -> 26 = (26**0)*26
AA -> 27 = (26**0)*1  + (26**1)*1
AB -> 28 = (26**0)*2  + (26**1)*1
AZ -> 52 = (26**0)*26 + (26**1)*1  # recall that we have (0, 'Z'), (1, 'A')

Reversing the column_id and enumerate() allows us to use the index as the exponent for 26. The rest is now trivial.

LETTERS.index(j): gives us the index of the letter in LETTERS

sum(): takes a list of numbers and returns the total.

Upvotes: 1

jmcnamara
jmcnamara

Reputation: 41644

Here is one way to do it. It is a variation on code in the XlsxWriter module:

def col_to_num(col_str):
    """ Convert base26 column string to number. """
    expn = 0
    col_num = 0
    for char in reversed(col_str):
        col_num += (ord(char) - ord('A') + 1) * (26 ** expn)
        expn += 1

    return col_num


>>> col_to_num('A')
1
>>> col_to_num('AB')
28
>>> col_to_num('ABA')
729
>>> col_to_num('AAB')
704

Upvotes: 8

sharat87
sharat87

Reputation: 7536

Here's what I use (wrote before I found this page):

def col_to_index(col):
    return sum((ord(c) - 64) * 26**i for i, c in enumerate(reversed(col))) - 1

And some runs:

>>> col_to_index('A')
1
>>> col_to_index('AB')
28
>>> col_to_index('ABCD')
19010

Upvotes: 1

Devon
Devon

Reputation: 1093

One-liners tested in Python 2.7.1 and 3.5.2

excel_col_num = lambda a: 0 if a == '' else 1 + ord(a[-1]) - ord('A') + 26 * excel_col_num(a[:-1])

excel_col_name = lambda n: '' if n <= 0 else excel_col_name((n - 1) // 26) + chr((n - 1) % 26 + ord('A'))

Multi-liners likewise

def excel_column_name(n):
    """Number to Excel-style column name, e.g., 1 = A, 26 = Z, 27 = AA, 703 = AAA."""
    name = ''
    while n > 0:
        n, r = divmod (n - 1, 26)
        name = chr(r + ord('A')) + name
    return name

def excel_column_number(name):
    """Excel-style column name to number, e.g., A = 1, Z = 26, AA = 27, AAA = 703."""
    n = 0
    for c in name:
        n = n * 26 + 1 + ord(c) - ord('A')
    return n

def test (name, number):
    for n in [0, 1, 2, 3, 24, 25, 26, 27, 702, 703, 704, 2708874, 1110829947]:
        a = name(n)
        n2 = number(a)
        a2 = name(n2)
        print ("%10d  %-9s  %s" % (n, a, "ok" if a == a2 and n == n2 else "error %d %s" % (n2, a2)))

test (excel_column_name, excel_column_number)
test (excel_col_name, excel_col_num)

All tests print

         0             ok
         1  A          ok
         2  B          ok
         3  C          ok
        24  X          ok
        25  Y          ok
        26  Z          ok
        27  AA         ok
       702  ZZ         ok
       703  AAA        ok
       704  AAB        ok
   2708874  EXCEL      ok
1110829947  COLUMNS    ok

Upvotes: 22

Asthmanaut
Asthmanaut

Reputation: 21

You could use this oneliner using comprehension and string that is fairly easy to use:

sum([string.ascii_lowercase.index(c) + 26 ** i for i,c in enumerate(col_letters)])

Upvotes: 1

dingles
dingles

Reputation: 1768

I made this one-liner:

colNameToNum = lambda cn: sum([((ord(cn[-1-pos]) - 64) * 26 ** pos) for pos in range(len(cn))])

It works by iterating through the letters in reverse order and multiplying by 1, 26, 26 * 26 etc, then summing the list. This method would be compatible with longer strings of letters, too.

I call it with:

print(colNameToNum("AA")) # 27

or

print(colNameToNum("XFD")) # the highest column allowed, I believe. Result = 16384

Upvotes: 1

char101
char101

Reputation: 1281

For index that starts from zero (e.g. A = 0, B = 1, and so on):

def col_to_index(col):
    A = ord('A')
    return sum(i * 26 + (ord(c) - A) for i, c in enumerate(col[::-1].upper()))

Upvotes: 0

Chad
Chad

Reputation: 181

Using openpyxl

import openpyxl
(column_string, row) = openpyxl.cell.coordinate_from_string(address)
column = openpyxl.cell.column_index_from_string(column_string)

Upvotes: 6

Sylvain
Sylvain

Reputation: 1233

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])

Upvotes: 61

Visicalc Visionary
Visicalc Visionary

Reputation: 11

After reading this, I decided to find a way to do it directly in Excel cells. It even accounts for columns after Z.

Just paste this formula into a cell of any row of any column and it will give you the corresponding number. 

=IF(LEN(SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),""))=2,
 CODE(LEFT(SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),""),1))-64*26)+
 CODE(RIGHT(SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),""),1)-64),
 CODE(SUBSTITUTE(ADDRESS(ROW(),COLUMN(),4),ROW(),""))-64)

The theme here was to grab the letter of the column, get the Code() of it and subtract 64, based on the fact that the ASCII character code for letter A is 64.

Upvotes: 1

sliders_alpha
sliders_alpha

Reputation: 2454

just do :

print ws.Range("E2").Column

call example :

from win32com import client
xl = client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open("c:/somePath/file.xls")
xl.Visible = 1
ws = wb.Sheets("sheet 1")
print ws.Range("E2").Column

result :

>>5

Upvotes: 0

Chris
Chris

Reputation: 1702

This should do, in VBA, what you're looking for:

Function columnNumber(colLetter As String) As Integer

    Dim colNumber As Integer
    Dim i As Integer

    colLetter = UCase(colLetter)
    colNumber = 0
    For i = 1 To Len(colLetter)
        colNumber = colNumber + (Asc(Mid(colLetter, Len(colLetter) - i + 1, 1)) - 64) * 26 ^ (i - 1)
    Next

    columnNumber = colNumber

End Function

You can use it as you would an Excel formula--enter column, in letters, as a string (eg, "AA") and should work regardless of column length.

Your code breaks when dealing with three letters because of the way you're doing the counting--you need to use base 26.

Upvotes: 2

Joubarc
Joubarc

Reputation: 1216

I'm not sure I understand properly, do you want to "translate" the referenced C# code to python? If so, you were on the right track; just modify it so:

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

Upvotes: 0

Related Questions