Convert spreadsheet number to column letter

Question

I'm looking for the opposite to this Q&A: Convert an excel or spreadsheet column letter to its number in Pythonic fashion.

or this one but in python How to convert a column number (eg. 127) into an excel column (eg. AA)

Answer 1

start_index = 1   #  it can start either at 0 or at 1
letter = ''
while column_int > 25 + start_index:   
    letter += chr(65 + int((column_int-start_index)/26) - 1)
    column_int = column_int - (int((column_int-start_index)/26))*26
letter += chr(65 - start_index + (int(column_int)))

Answer 2

The xlsxwriter library includes a conversion function, xlsxwriter.utility.xl_col_to_name(index) and is on github

here is a working example:

>>> import xlsxwriter 
>>> xlsxwriter.utility.xl_col_to_name(10)
'K'
>>> xlsxwriter.utility.xl_col_to_name(1)
'B'
>>> xlsxwriter.utility.xl_col_to_name(0)
'A'

Notice that it's using zero-indexing.

Answer 3

The openpyxl library includes the conversion function (amongst others) which you are looking for, get_column_letter:

>>> from openpyxl.utils.cell import get_column_letter
>>> get_column_letter(1)
'A'
>>> get_column_letter(10)
'J'
>>> get_column_letter(3423)
'EAQ'

Answer 4

My recipe for this was inspired by another answer on arbitrary base conversion (https://stackoverflow.com/a/24763277/3163607)

def n2a(n):
    d, m = divmod(n,26) # 26 is the number of ASCII letters
    return '' if n < 0 else n2a(d-1)+chr(m+65) # chr(65) = 'A'

Example:

print (-1,n2a(-1))
print (0,n2a(0))
for i in range(23,30):
    print (i,n2a(i))

outputs

-1
0 A
23 X
24 Y
25 Z
26 AA
27 AB
28 AC
29 AD

EDIT: I added n2a(0) to clarify that it is zero-based. I also changed the function a little to avoid infinite recursion if given a negative number.

Answer 5

Edited after some tough love from Meta

The procedure for this involves dividing the number by 26 until you've reached a number less than 26, taking the remainder each time and adding 65, since 65 is where 'A' is in the ASCII table. Read up on ASCII if that doesn't make sense to you.

Note that like the originally linked question, this is 1-based rather than zero-based, so A -> 1, B -> 2.

def num_to_col_letters(num):
    letters = ''
    while num:
        mod = (num - 1) % 26
        letters += chr(mod + 65)
        num = (num - 1) // 26
    return ''.join(reversed(letters))

Example output:

for i in range(1, 53):
    print(i, num_to_col_letters(i))
1 A
2 B
3 C
4 D
...
25 Y
26 Z
27 AA
28 AB
29 AC
...
47 AU
48 AV
49 AW
50 AX
51 AY
52 AZ

Answer 6

Just for people still interest in this. The chosen answer by @Marius gives wrong outputs in some cases, as commented by @jspurim. Here is the my answer.

import string
def convertToTitle(num):
    title = ''
    alist = string.uppercase
    while num:
        mod = (num-1) % 26
        num = int((num - mod) / 26)  
        title += alist[mod]
    return title[::-1]

Answer 7

Recursive one line solution w/o libraries

def column(num, res = ''):
   return column((num - 1) // 26, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'[(num - 1) % 26] + res) if num > 0 else res

Answer 8

Recursive Implementation

import string
def spreadsheet_column_encoding_reverse_recursive(x):
    def converter(x):
        return (
            ""
            if x == 0
            else converter((x - 1) // 26) + string.ascii_uppercase[(x - 1) % 26]
        )

    return converter(x)

Iterative Implementations

Version 1: uses chr, ord

def spreadsheet_column_encoding_reverse_iterative(x):
    s = list()

    while x:
        x -= 1
        s.append(chr(ord("A") + x % 26))
        x //= 26

    return "".join(reversed(s))

Version 2: Uses string.ascii_uppercase

import string
def spreadsheet_column_encoding_reverse_iterative(x):
    s = list()

    while x:
        x -= 1
        s.append(string.ascii_uppercase[x % 26])
        x //= 26

    return "".join(reversed(s))

Version 3: Uses divmod, chr, ord

def spreadsheet_column_encoding_reverse_iterative(x):
    s = list()

    while x:
        x, remainder = divmod(x - 1, 26)
        s.append(chr(ord("A") + remainder))

    return "".join(reversed(s))

Answer 9

def _column(aInt):
    return chr((aInt - 1) // 26 + 64) + chr((aInt - 1) % 26 + 1 + 64) if aInt > 26 else chr(aInt + 64)
    
print _column(1)
print _column(27)
print _column(50)
print _column(100)
print _column(260)
print _column(270)

Output: A AA AX CV IZ JJ

Answer 10

This simple Python function works for columns with 1 or 2 letters.

def let(num):       

alphabeth = string.uppercase
na = len(alphabeth)

if num <= len(alphabeth):
    letters = alphabeth[num-1]
else:
    letters = alphabeth[ ((num-1) / na) - 1 ] +  alphabeth[((num-1) % na)]            

return letters

Answer 11

import gspread

def letter2num(col_letter: str) -> int:
    row_num, col_num = gspread.utils.a1_to_rowcol(col_letter + '1')
    return col_num


def num2letter(col_num: int) -> str:
    return gspread.utils.rowcol_to_a1(1, col_num)[:-1]


# letter2num('D') => returns 4
# num2letter(4) => returns 'D'

Answer 12

Here is a recursive solution:

def column_num_to_string(n):
    n, rem = divmod(n - 1, 26)
    char = chr(65 + rem)
    if n:
        return column_num_to_string(n) + char
    else:
        return char

column_num_to_string(28)
#output: 'AB'

The inverse can also be defined recursively, in a similar way:

def column_string_to_num(s):
    n = ord(s[-1]) - 64
    if s[:-1]:
        return 26 * (column_string_to_num(s[:-1])) + n
    else:
        return n
    
column_string_to_num("AB")
#output: 28

Answer 13

Using xlsxwriter

import gspread
import numpy as np
import xlsxwriter

.....You code.......

array = np.array(wks.get_all_values())


row_count = 0
col_count = 0
for a in array:
    col_count = 0
    row_count += 1
    for b in a:
        col_count += 1
        print(f"{xlsxwriter.utility.xl_col_to_name(col_count-1)}{row_count} {str(b)}")

Answer 14

Just to complicate everything a little bit I added caching, so the name of the same column will be calculated only once. The solution is based on a recipe by @Alex Benfica

import string


class ColumnName(dict):
    def __init__(self):
        super(ColumnName, self).__init__()
        self.alphabet = string.uppercase
        self.alphabet_size = len(self.alphabet)

    def __missing__(self, column_number):
        ret = self[column_number] = self.get_column_name(column_number)
        return ret

    def get_column_name(self, column_number):
        if column_number <= self.alphabet_size:
            return self.alphabet[column_number - 1]
        else:
            return self.alphabet[((column_number - 1) / self.alphabet_size) - 1] + self.alphabet[((column_number - 1) % self.alphabet_size)]

Usage example:

column = ColumnName()

for cn in range(1, 40):
    print column[cn]

for cn in range(1, 50):
    print column[cn]

Answer 15

import math

num = 3500
row_number = str(math.ceil(num / 702))
letters = ''
num = num - 702 * math.floor(num / 702)
while num:
    mod = (num - 1) % 26
    letters += chr(mod + 65)
    num = (num - 1) // 26
result = row_number + ("".join(reversed(letters)))
print(result)

Answer 16

an easy to understand solution:

def gen_excel_column_name(col_idx, dict_size=26):
    """generate column name for excel

    Args:
        col_idx (int): column index, 1 based.
        dict_size (int, optional): NO. of letters to use. Defaults to 26 (A~Z).

    Returns:
        str: column name. e.g. A, B, C, AA, AB, AC
    """
    if col_idx < 1:
        return ''
    
    # determine how many letters in the result
    l = 1  # length of result
    capcity = dict_size  # number of patterns when length is l
    while col_idx > capcity:
        col_idx -= capcity
        l += 1
        capcity *= dict_size

    res = []
    col_idx -= 1 # now col_idx is a dict_size system. when dict_size = 3, l = 2,  col_idx=2 means 02, col_idx=3 means 10 
    while col_idx > 0:
        d = col_idx % dict_size
        res.append(d)
        col_idx = col_idx // dict_size
    # padding leading zeros
    while len(res) < l:
        res.append(0)

    # change digits to letters and reverse
    res = [chr(65 + d) for d in reversed(res)]
    return ''.join(res)

for i in range(1, 42):
    print(i, gen_excel_column_name(i, 3))

part of the output:

1 A
2 B
3 C
4 AA
5 AB
6 AC
7 BA
8 BB
9 BC
10 CA
11 CB
12 CC
13 AAA
14 AAB
15 AAC
16 ABA
17 ABB
18 ABC
19 ACA
20 ACB
21 ACC
22 BAA
23 BAB
24 BAC
25 BBA
26 BBB
27 BBC
28 BCA
29 BCB
30 BCC
31 CAA
32 CAB
33 CAC
34 CBA
35 CBB
36 CBC
37 CCA
38 CCB
39 CCC
40 AAAA
41 AAAB

Answer 17

Here is a modified version of the accepted answer that won't break after ZZ.

• It uses a single while loop and the divmod() function to simplify the calculations.
• start_index can be 0 or 1.
• The divmod() function returns both the quotient and the remainder when dividing two numbers, which in this case are column_int - start_index and 26.
• The remainder is used to generate the next character for the column name, and the quotient is used as the new column_int for the next iteration.
• The loop continues until column_int becomes zero.

def int_to_excel_column(column_int, start_index):
    
    letter = ''

    while column_int > 0:
        column_int, remainder = divmod(column_int - start_index, 26)
        letter = chr(65 + remainder) + letter

    return letter

Example:

column_name = int_to_excel_column(28)  # Output: 'AB'
column_name = int_to_excel_column(703)  # Output: 'AAA'

Answer 18

For those looking for a solution that works for any integer number.

def interger_to_letter(column_int):
    start_index = 0
    column_int = column_int + start_index - 1
    letter = ""
    if column_int < 0: 
        return letter
    
    char = 0
    while column_int >= 0:
        mod = column_int%26**(char+1) + 1
        letter = chr(int(mod/26**(char)) + 64) + letter
        column_int -= mod
        char += 1
        if column_int == 0:
            break
        
    return letter