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:

**Excel Column codes do not have a zero.**

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