Am I missing something or is this Microsoft algorithm for calculating the excel column characters incorrect?

Question

I'm trying to write a function in Python that takes in a column number and outputs the corresponding Excel column code (for example: 5 -> "E", 27 -> "AA"). I tried implementing the algorithm given here: http://support.microsoft.com/kb/833402, which is the following visual basic:

Function ConvertToLetter(iCol As Integer) As String
   Dim iAlpha As Integer
   Dim iRemainder As Integer
   iAlpha = Int(iCol / 27)
   iRemainder = iCol - (iAlpha * 26)
   If iAlpha > 0 Then
      ConvertToLetter = Chr(iAlpha + 64)
   End If
   If iRemainder > 0 Then
      ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
   End If
End Function

My python version:

def excelcolumn(colnum):
    alpha = colnum // 27
    remainder = colnum - (alpha*26)
    out = ""
    if alpha > 0:
        out = chr(alpha+64) 
    if remainder > 0:
        out = out + chr(remainder+64) 
    return out

This works fine until column number 53 which results in "A[", as alpha = 53 // 27 == 1 and thus remainder = 53 - 1*26 == 27 meaning the second character chr(64+27) will be "[". Am I missing something? My VBA skills are quite lackluster so that might be the issue.

edit: I am using Python 3.3.1

Mark Ransom · Accepted Answer · 2013-12-12T17:48:27.910

2

The Microsoft formula is incorrect. I'll bet they never tested it beyond 53. When I tested it myself in Excel it gave the same incorrect answer that yours did.

Here's how I'd do it:

def excelcolumn(colnum):
    alpha, remainder = colnum // 26, colnum % 26
    out = "" if alpha == 0 else chr(alpha - 1 + ord('A'))
    out += chr(remainder + ord('A'))
    return out

Not that this assumes a 0-based column number while the VBA code assumes 1-based.

If you need to extend beyond 701 columns you need something slightly different as noted in the comments:

def excelcolumn(colnum):
    if colnum < 26:
        return chr(colnum + ord('A'))
    return excelcolumn(colnum // 26 - 1) + chr(colnum % 26 + ord('A'))

edited Dec 12 '13 at 17:48

answered Dec 12 '13 at 16:38

Mark Ransom

299,747
42
398
622

Nice, this works flawlessly up to column 701 ("ZZ") (which is more than enough for my purposes). After that point the alpha goes beyond the capital letter character indices and some further restrictions are necessary. It seems that a more generalizable algorithm would be hard to implement cleanly. – Ben Southgate Dec 12 '13 at 16:46
If you want to go beyond "ZZ", you should use a recursive function. – sancho.s ReinstateMonicaCellio Dec 12 '13 at 16:48
This is a great generalizable answer (Python 2 heads up), more than I could manage with my current head cold grogginess: http://stackoverflow.com/a/182009/1718488 – Ben Southgate Dec 12 '13 at 17:15
@ben - Great! I was sure this question should have an answer for the generic case in SO. Actually, I think I had code for this that I used some time ago, probably in VBA, and probably based on something I found here in SO... Found it http://stackoverflow.com/questions/12796973/vba-function-to-convert-column-number-to-letter – sancho.s ReinstateMonicaCellio Dec 12 '13 at 20:15

score 1 · Answer 2 · edited May 23 '17 at 10:26

I am going to answer your specific question:

... is this Microsoft algorithm for calculating the excel column characters incorrect?

YES. Generally speaking, when you want to have the integer division (typically called DIV) of two numbers, and the remainder (typically called MOD), you should use the same value as the denominator. Thus, you should use either 26 or 27 in both places.

So, the algorithm is incorrect (and it is easy to see that with iCol=27, where iAlpha=1 and iRemainder=1, while it should be iRemainder=0).

In this particular case, the number should be 26. Since this gives you numbers starting at zero, you should probably add ascii("A") (=65), generically speaking, instead of 64. The double error made it work for some cases.

The (hardly acceptable) confusion may stem from the fact that, from A to Z there are 26 columns, from A to ZZ there are 26*27 columns, from A to ZZZ there are 26*27*27 columns, and so on.

Code that works for any column, and non-recursive:

def excelcolumn(colnum):
    if colnum < 1:
        raise ValueError("Index is too small")
    result = ""
    while True:
        if colnum > 26:
            colnum, r = divmod(colnum - 1, 26)
            result = chr(r + ord('A')) + result
        else:
            return chr(colnum + ord('A') - 1) + result

(taken from here).

score 1 · Answer 3 · answered Dec 14 '13 at 12:57

Here is one way to do it:

def xl_col_to_name(col_num):
    col_str = ''

    while col_num:
        remainder = col_num % 26

        if remainder == 0:
            remainder = 26

        # Convert the remainder to a character.
        col_letter = chr(ord('A') + remainder - 1)

        # Accumulate the column letters, right to left.
        col_str = col_letter + col_str

        # Get the next order of magnitude.
        col_num = int((col_num - 1) / 26)

    return col_str

Which gives:

>>> xl_col_to_name(5)
'E'
>>> xl_col_to_name(27)
'AA'
>>> xl_col_to_name(256)
'IV'
>>> xl_col_to_name(1000)
'ALL'

This is taken from the utility functions in the XlsxWriter module.

This question actually came about while using your infinitely helpful and awesome package! I didn't think to actually look within xlsxwriter itself until after I wrote the question, a bit of stupidity on my part. Again, really great job on xlsxwriter. — Ben Southgate, Dec 16 '13 at 15:02

Am I missing something or is this Microsoft algorithm for calculating the excel column characters incorrect?

3 Answers3