Convert column number to letter excel python

asked May 26, 2014 at 1:44

2

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

answered May 26, 2014 at 3:14

sundar natarajsundar nataraj

8,3062 gold badges31 silver badges44 bronze badges

3

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.

answered Jan 14, 2015 at 21:47

travisatravisa

9278 silver badges12 bronze badges

4

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'

answered Oct 10, 2018 at 0:45

RomanRoman

6347 silver badges11 bronze badges

0

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

import string

def n2a(n,b=string.ascii_uppercase):
   d, m = divmod(n,len(b))
   return n2a(d-1,b)+b[m] if d else b[m]

Example:

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

outputs

23 X
24 Y
25 Z
26 AA
27 AB
28 AC
29 AD

answered Jun 3, 2016 at 0:19

2

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]

answered Feb 28, 2015 at 14:07

LukeLuke

6601 gold badge9 silver badges20 bronze badges

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

answered May 26, 2014 at 3:18

MariusMarius

55.8k15 gold badges103 silver badges100 bronze badges

8

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

answered Aug 19, 2018 at 20:16

AxalixAxalix

2,7711 gold badge19 silver badges35 bronze badges

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

answered May 6, 2020 at 7:38

BrayoniBrayoni

5964 silver badges14 bronze badges

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(50)
print _column(100)
print _column(260)
print _column(270)

Output: A AX CV IZ JJ

answered Mar 2, 2021 at 12:59

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

answered Oct 16, 2015 at 11:25

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

answered Jul 21, 2020 at 11:13

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]

answered Feb 28, 2017 at 10:13

AndreiAndrei

1078 bronze badges

1

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)

answered Oct 15, 2019 at 2:56

RickyRicky

1192 silver badges9 bronze badges

1

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'

answered May 27, 2020 at 23:43

George CGeorge C

1,05813 silver badges29 bronze badges

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

answered Jul 19 at 9:49

How do I convert a column of numbers to letters in Excel?

What is column letter in Excel?

How do I change column numbers to letters in VBA?

How do you change columns from numbers to letters in Excel on Mac?