2

I have a list with alphanumeric characters like as shown below

l1 = ['G1','L1']

I would like to know whether we have something like below

for i in range(l1):  #this doesn't work because range is only for numeric values
for i in range(G1:L1): #this also doesn't work

However, I want the i value at each run to change from G1to H1 to I1 to J1 to K1 to L1

The Great
  • 7,215
  • 7
  • 40
  • 128

2 Answers2

4

Range always expects a number and cannot work with strings.
However, you can use the built-in ord() function to convert letters to numbers and then use the chr() function to convert them back from numbers to ASCII characters.

Code

a = [chr(c)+'1' for c in range(ord('G'), ord('M'))]
print(a)

Output

['G1', 'H1', 'I1', 'J1', 'K1', 'L1']

Update: Solution for double characters.

Doing it for double characters is a little more complicated, but this StackOverflow answer has a solution to that. You can simply use the from_excel() and to_excel() functions from that answer and replace them in my above code as follows.

Code

a = [to_excel(i) for i in range(from_excel('G'), from_excel('AG'))]
print(a)

Output

['G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF']
DollarAkshay
  • 2,063
  • 1
  • 21
  • 39
  • @TheGreat don't forget to accept the answer that solved your problem :) – DollarAkshay Mar 22 '22 at 10:04
  • It doesn't work for AA to AG ? – Corralien Mar 22 '22 at 10:05
  • btw, one quick question - it doesn't work if I need `Z`? doing this `range(ord('G'), ord('Z'))`, gets only till `Y`. I can't add +1 to the range because it throws error. Can let me know how can we get `Z` value? – The Great Mar 22 '22 at 10:06
  • @TheGreat you can just do `ord('Z') + 1` – DollarAkshay Mar 22 '22 at 10:07
  • @Corralien Yeah it only works for single characters. I could write a custom function that does that if that if OP wants – DollarAkshay Mar 22 '22 at 10:09
  • @DollarAkshay - Just so you know my question is in relation to this problem that I am trying to solve - https://stackoverflow.com/questions/71567484/pandas-compare-index-and-column-between-excel-and-dataframe-to-enter-value . However, am not able to find a solution to this. Is this something that you would be able to help me with? – The Great Mar 22 '22 at 10:19
  • 1
    @TheGreat I have updated my answer. Let me know if that solves your use case. – DollarAkshay Mar 22 '22 at 10:39
2

You can use:

from openpyxl.utils import coordinate_to_tuple, get_column_letter

def excel_range(start, end):
    t1 = coordinate_to_tuple(start)
    t2 = coordinate_to_tuple(end)
    rows, cols = zip(t1, t2)
    cells = []
    for r in range(rows[0], rows[1]+1):
        for c in range(cols[0], cols[1]+1):
            cells.append(f'{get_column_letter(c)}{r}')
    return cells

cells = excel_range('AA1', 'AC4')

Output:

>>> cells
['AA1',
 'AB1',
 'AC1',
 'AA2',
 'AB2',
 'AC2',
 'AA3',
 'AB3',
 'AC3',
 'AA4',
 'AB4',
 'AC4']
Corralien
  • 109,409
  • 8
  • 28
  • 52