13,050,617 members (83,022 online)
Technical Blog
alternative version

#### Stats

17.8K views
5 bookmarked
Posted 14 Jan 2012

# Converting Excel Column Numbers

, 14 Jan 2012
 Rate this:
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:

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
' Taking a log will provide a rough estimate of the power that
' 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 .

## Share

 Software Developer (Senior) United States
No Biography provided

## You may also be interested in...

 First Prev Next
 Another helper library JamesHoward97216-Jan-12 21:47 JamesHoward972 16-Jan-12 21:47
 Another post with the same purpose Jerome Vibert14-Jan-12 21:47 Jerome Vibert 14-Jan-12 21:47
 Re: Another post with the same purpose Cyborgx3715-Jan-12 16:09 Cyborgx37 15-Jan-12 16:09
 Re: Another post with the same purpose Jerome Vibert18-May-12 23:15 Jerome Vibert 18-May-12 23:15
 Last Visit: 31-Dec-99 18:00     Last Update: 25-Jul-17 16:37 Refresh 1

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Web02 | 2.8.170713.1 | Last Updated 14 Jan 2012