0

Say i have a cell in position (AZ, 5) in an excel spreadsheet and would like to convert the column reference AZ into a number such that A -> 1, .. , Z -> 26, AA -> 27, AZ -> 52. How can i do so.

My attempt doesn't seem optimal:

import string

di=dict(zip(string.ascii_uppercase,[ord(c)%32 for c in string.ascii_uppercase]))

def column_to_number(ref):
    if len(ref) == 1:
        number = di[ref]
    else:
        for i in ref:
            # don't know what to do here
    return number
Alan Kavanagh
  • 9,425
  • 7
  • 41
  • 65
j_eko
  • 1

2 Answers2

0

In general,

numerical value of x_1x_2..x_n = numerical value of x_1 * 26^(n-1) + numerical value of x_2 * 26^(n-2) + ... + numerical value of x_n = numerical value of x_1 * 26^(n-1) + numerical value of x_2 x_3...x_n

So.. 

import math
def column_to_number(s):
   order_of_letter=ord(s[0])-ord('A')+1 
   if len(s)==1:
     return order_of_letter 
   else:
     return (int(math.pow(26, len(s)-1)*order_of_letter+column_to_number(s[1:])))

Also, see Convert an excel or spreadsheet column letter to its number in Pythonic fashion

user17144
  • 428
  • 3
  • 18
  • Please provide more detail in your answer. "Code-only" answers are discouraged. – OneHoopyFrood Oct 21 '19 at 16:23
  • It is pretty clear that they have to substitute my function for theirs. Do you mean "explain the logic" by "provide more detail"? – user17144 Oct 21 '19 at 16:35
  • Yeah, that'd be a fair interpretation. The community just discourages code-only answers as it limits their usefulness to the original question only. The goal is to create a knowledge store wherever we can, so explaining why your code fixes the problem would be super useful! – OneHoopyFrood Oct 23 '19 at 03:16
  • OK. I added an explanation. – user17144 Oct 23 '19 at 10:33
0

try this one-liner, no imports needed:

col = "AZ"
col_number = sum([(ord(col.upper()[-i-1])-64)*26**i for i in range(len(col))])
Poe Dator
  • 4,535
  • 2
  • 14
  • 35