Convert the letter of an Excel column or table to its number in Pythonic

Question

Is there a more pythonic way to convert excel-style columns to numbers (starting from 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 

The code is executed:

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

Three letters do not work.

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

Link: question answered in C #

Answer 1

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-line one that uses shorthand (doesn't check input and becomes 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]) 

Answer 2

Here is one way to do it. This is the code option 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 

Answer 3

Using openpyxl

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

Answer 4

Single line 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-line lines also

 def excel_column_name(n): """Number to Excel-style column name, eg, 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, eg, 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 are printed.

  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 

Answer 5

After installing the openpyxl module, you can add the following to the console:

 import openpyxl from openpyxl.utils import get_column_letter, column_index_from_string workbook = openpyxl.load_workbook('your_workbook.xlsx') sheet = wb.get_sheet_by_name('your_sheet_from_workbook') print(get_column_letter(1)) print(column_index_from_string('A')) 

Just change the letters and numbers to suit your needs or create a for-loop to complete the task for the entire project. Hope this helps. Greetings.

Answer 6

In VBA, this should do what you are 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, like the Excel formula, - enter the column with letters in the form of a string (for example, "AA") and should work regardless of the length of the column.

Your code breaks when working with three letters because of how you do the counting - you need to use base 26.

Answer 7

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

Just paste this formula into the 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 column letter, get Code() and subtract 64 based on the fact that the ASCII character code for the letter A is 64.

Answer 8

I made this single line image:

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

It works by repeating the letters in reverse order and multiplying by 1, 26, 26 * 26, etc., and then summing the list. This method will also be compatible with longer lines of letters.

I call it with

print (colNameToNum ("AA")) # 27

or

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

Answer 9

I'm not sure I understand correctly, do you want to "translate" C # reference code into python? If so, you are on the right track; just change it like this:

 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 

Answer 10

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 

Answer 11

Compatibility and elegant version of Ruby :

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

Answer 12

For an index starting at zero (for example, A = 0, B = 1, etc.):

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

Answer 13

You can use this oneliner using an understanding and a string that are fairly easy to use:

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

Answer 14

Here is 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 

Answer 15

Using:

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

This part contains several parts, so the explanation is given below:

column_id[::-1] : flips the line, for example, converts 'AZ' to 'ZA' , there is a good reason for this, which we will see a little later.

enumerate() : iterates, 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') 

The column_id and enumerate() allows us to use the index as an 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 a total.