0

Excel files can contain up to 16,384 (214) columns. The first 26 columns are labeled "A", "B", "C", and so on. After that it is "AA", "AB", "AC", ..., "BA", "BB", "BC", ..., and so on, but two-letter labels only support an additional 262=676 columns. So eventually we see "AAA", "AAB", "AAC", and so on, to a max of "XFD".

I'd like to write a Python function that converts a column offset/index to a column label.

Offset Label
0 A
1 B
... ...
25 Z
26 AA
27 AB
... ...
700 ZY
701 ZZ
702 AAA
703 AAB
... ...
16383 XFD

For the first 26 columns this is trivial.

>>> offset = 0
>>> chr(offset + 65)
'A'

But how do I generalize this to support all valid inputs and outputs?

I tried adapting this code and converting the offsets to numbers in base 26, and then using chr to convert each place value to a letter. But I couldn't get it to work as intended.

def numberToBase(n, b):
    if n == 0:
        return [0]
    digits = []
    while n:
        digits.append(int(n % b))
        n //= b
    return digits[::-1]
Daniel Standage
  • 8,136
  • 19
  • 69
  • 116
  • Re your linked base conversion answer, that won't work because Excel column names are not strictly base 26 [Here's a C# answer you might be able to translate to Python](https://stackoverflow.com/a/182924/445425) – chris neilsen Feb 16 '22 at 22:32
  • Looks like it has already been answered here https://stackoverflow.com/a/182009/14159796 – Cubix48 Feb 16 '22 at 22:41

1 Answers1

1

Here is my version using recursion:

def column_offset_to_column_name(index):
    if index < 0:
        return ""
    quotient, remainder = divmod(index, 26)
    return column_offset_to_column_name(quotient - 1) + chr(remainder + 65)
Cubix48
  • 2,607
  • 2
  • 5
  • 17