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)

Lew

did u try that in python – Quicklime

@sundar nataraj サンダーナタラジ i sure did. I tried the code here: bytes.com/topic/python/answers/45129-convert-numbers-letters . I tried a few examples in there. But since the site is so horribly organized. I can't figure what code works and doesn't. Even people getting answers on that page can't figure it out. – Lew

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)))

Quicklime

Any idea how that example can be modified for a zero rather than 1-based solution? A -> 0, B -> 1 .... my modification so far: n += 1before the while loop – Coreligionist

Starting with n += 1 is probably the simplest way to do it. There's no symbol for "0", so it's not a normal base 26 system. In base ten, "20" represents 2*10+0*1. In the spreadsheet, you can't represent 2*26+0*1. You have to use 1*26+26*1, which is "AZ". See paradise.caltech.edu/ist4/lectures/… – Dissonant

Strings are immutable, thus, prepending to a string is expensive since everything must move. Consider using a mutable alternative like a list and converting to string "".join(lst) – Enriqueenriqueta

for column_int = 4000 it gives me ÙV what is rather unusual :) – Unbodied

Yes, this one only works until ZZ, and then it breaks down. – Unesco

I recommend avoiding this since it only handles columns up to 2 letters (e.g. ZZ works, but it will fail for AAA). If you're sure you'll have less than 700 cols this is OK to use but using xlsxwriter.utility.xl_col_to_name is more general. – Trunks

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.

Soper

This library is great, it's a good idea to use a well know working library instead of custom code – Bullheaded

+1 , but xlsxwriter.utility.xl_col_to_name(28) is giving AC instead of AB, By subtracting input number with 1 it is giving expected output as AB, but do you know why it is happening – Walsingham

@akashkarothiya xlsxwriter uses 0-based indexing (as does python) – Spermatium

Also the xlrd library has a conversion function: xlrd.formula.colname(0) returns 'A' – Allsopp

It breaks after 99104947832496131. Any number higher and it becomes inaccurate. – Bandler

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'

Caitlyncaitrin

The Microsoft spec page for Excel says the max number of columns is 16384 (XFD), but openpyxl allows up to 18279 (ZZZ). If you try to load a sheet with that column in excel, it will not like it and will move the content to the next available column when repairing it. – Malynda

Answer 4

My recipe for this was inspired by another answer on arbitrary base conversion (https://mcmap.net/q/100618/-python-elegant-inverse-function-of-int-string-base)

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.

Allsopp

Concise and zero-based! – Jessiajessica

Be aware that this indexing is zero-based! – Underpinnings

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

Canute

This gives wrong output for num_to_col_letters(26) it outputs "A@" – Purveyance

I saw the edit proposed by @coldfix - this answer doesn't work and a working version was proposed in good faith as an edit. For full disclosure: I wasn't sure how to deal with that and asked this question on meta about it. Anyway - my recommendation is that the edit would be better added as a new answer - or that the original answer should have a look at that edit and decide whether to change this answer. – Noctilucent

P.S. The answer by @user1344186 works on these edge cases properly using a similar approach. – Noctilucent

@JRichardSnape I agree that my answer is basically broken and the edit more or less provides a brand new algorithm rather than fixing minor problems with mine. – Canute

@jason_cant_code still seems to be active on the site so maybe they can be persuaded to switch the accepted answer. – Canute

@Canute Well, why don't you just fix the answer? The point of this site is to provide correct information, and you yourself fixing wrong answer of your own certainly is not defacing or anything. – Seminarian

@Seminarian The answer should be fixed now, although it might be too late to reverse the effects of the hammering it took from Meta. – Canute

Sorry - i didn't expect the hammering that my approach triggered. I'll certainly be more cautious about linking to anything from meta again - I was just trying to query my own actions not yours. I think you've done all the right things and have reversed my downvote FWIW. – Noctilucent

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]

Lear

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

Nakashima

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))

Enriqueenriqueta

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

Synapsis

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

Loren

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'

Nakamura

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

Access

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)}")

Shedevil

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]

Fons

A couple issues with this implementation: it accepts negative numbers, where intuitively it should error; it fails on columns of three or more characters (eg column[703] should be AAA) – Wharve

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)

Swordfish

this will give valid results for column[703] and further – Swordfish

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

Cognition

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'

Ostrander

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

Tilton

Answer 19

Try this:

def n2c(n):
    return chr(n + 64) if n <= 26 else n2c((n - n % 26) // 26) + n2c(n % 26)

Examples:

print(n2c(17))  # Q
print(n2c(55))  # BC
print(n2c(165))  # FI
print(n2c(4999165))  # JXKEO

Eddins

Answer 20

def convertColumnNumberToExcelColumnLetters(column:int) -> str:
    letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'
    parts = []
    n = column
    m = column
    while n > 26:
        n,m = divmod(n,26)
        parts.append(n)
    if m == 0:
        parts[-1] -=1
        m = 26
    parts.append(m)
    chars = list(map(lambda i: letters[i-1], parts))
    return  ''.join(chars)

Gamesome