Converting Excel Column Numbers

I recently had to convert Excel column numbers (or rather, "codes") to and from a decimal representation.

At first, I figured the answer would be fairly straight forward, but it proved more challenging than I was expecting.

Of course, some people have been content to provide a simply mapping function along the lines of:

fun (int input) {
switch (input) {
case 1: return "A";
case 2: return "B";
}
}

I didn't want to use this because I'm like that. I like extensibility and I don't like 500 line functions. So I set about the task. A through Z went without a hitch - just add the index to 0x41 and get the ASCII code. Then I tried to represent AA and I realized that I had a big gaping problem with my number representation:

What is AA? It's 26 (represented by the first A) plus 1. That second A is a "1", not a "0". There are no 0s.

Fine. For numbers greater than 26, just divide and reminder the value. Right? Wrong.

You get to value 703 and suddenly things aren't working too well. Fine. For numbers greater than... but wait! What about my extensibility?!?

I'll save you the long winded version and simply provide the code snippet. I've included comments to clarify some of the finer points. (This code snippet is in VB.NET, but could be easily translated to just about any language.)

Public Shared Function IntegerToExcelColumnCode(ByVal columnIndex As Integer) As String        
    ' Taking a log will provide a rough estimate of the power that 
    ' we should start with. Because Excel's column
    ' numbering system has no representation for 0, the natural log 
    ' cannot give us a precise number because the
    ' numbering system doesn't have a true "base" 
    ' (though base 26 roughly corresponds for relatively low numbers).
    
    Dim l_startingExponent = CInt(Math.Ceiling(Math.Log(columnIndex) / Math.Log(26)))
    
    Dim l_remainder = columnIndex
    Dim l_code As String = ""
    For l_exponent = l_startingExponent To 0 Step -1
        ' Z for the previous place would be equal to A in the current 
        ' place except that there are no zeroes, so A in
        ' the current place is only valid if the value being represented 
        ' is greater than the value represented by A
        ' in the current place (otherwise, use Z in the next place). 
        ' The only exception is the value 1 as there is
        ' no "tenth's" (or rather, "twenty-sixth's") 
        ' place in this numbering system.
        If l_exponent = 0 OrElse l_remainder Mod Math.Pow(26, l_exponent) > 0 Then
            Dim l_placeValue = Math.Floor(l_remainder / Math.Pow(26, l_exponent))
            
            ' This should only ever happen as a result of guessing 
            ' the starting exponent too high. As zeroes are
            ' never valid, they should never occur.
            If l_placeValue > 0 Then
                l_remainder -= Math.Pow(26, l_exponent) * l_placeValue
                l_code &= Chr(&H40 + l_placeValue)
            End If
        ElseIf l_remainder > Math.Pow(26, l_exponent) Then
            Dim l_placeValue = Math.Floor((l_remainder - _
            Math.Pow(26, l_exponent)) / Math.Pow(26, l_exponent))
            
            ' This should only ever happen as a result of guessing 
            ' the starting exponent too high. As zeroes are
            ' never valid, they should never occur.
            If l_placeValue > 0 Then
                l_remainder -= Math.Pow(26, l_exponent) * l_placeValue
                l_code &= Chr(&H40 + l_placeValue)
            End If
        End If
    Next
    
    Return l_code
End Function

I hope this saves someone else the hour or so of pain that I experienced.

Visit my profile on CodeProject.