Repeating letters like excel columns?

Question

I want to create a list of string that resemble the column letters in Microsoft Excel. For example, after 26 columns, the next columns become AA, AB, AC, etc.

I have tried using the modulus operator, but I just end up with AA, BB, CC, etc...

import string

passes_through_alphabet = 0

for num, col in enumerate([_ for _ in range(40)]):
    if num % 26 == 0:
        passes_through_alphabet += 1
    excel_col = string.ascii_uppercase[num%26] * passes_through_alphabet
    print(num, excel_col)

0 A
1 B
2 C
3 D
...
22 W
23 X
24 Y
25 Z
26 AA
27 BB
28 CC
...

Answer 1

Based on this answer: https://stackoverflow.com/a/182009/6591347

def num_to_excel_col(n):
    if n < 1:
        raise ValueError("Number must be positive")
    result = ""
    while True:
        if n > 26:
            n, r = divmod(n - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(n + ord('A') - 1) + result

Answer 2

You can use itertools.product for this:

import string
import itertools
list(itertools.product(string.ascii_uppercase, repeat=2))

Output:

[('A', 'A'), ('A', 'B'), ('A', 'C'), ('A', 'D'), ...

Combining this with the first set of letters, and joining the pairs results in:

list(
    itertools.chain(
        string.ascii_uppercase, 
        (''.join(pair) for pair in itertools.product(string.ascii_uppercase, repeat=2))
))

Output:

['A', 'B', 'C', .. 'AA', 'AB', 'AC' .. 'ZZ']

To generalize, we define a generator that builds up bigger and bigger products. Note that the yield is only available in python 3.3+, but you can just use a for loop to yield each item if you're on python 2.

def excel_cols():
    n = 1
    while True:
        yield from (''.join(group) for group in itertools.product(string.ascii_uppercase, repeat=n))
        n += 1

list(itertools.islice(excel_cols(), 28))

output

['A', 'B', 'C', ... 'X', 'Y', 'Z','AA', 'AB']