Click here to Skip to main content
Click here to Skip to main content

Converting Excel Column Numbers

, 14 Jan 2012
Rate this:
Please Sign up or sign in to vote.
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 
    ' 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 .

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Cyborgx37
Software Developer (Senior)
United States United States
No Biography provided
Follow on   Google+

Comments and Discussions

 
QuestionAnother helper library PinmemberJamesHoward97216-Jan-12 21:47 
QuestionAnother post with the same purpose PinmemberJerome Vibert14-Jan-12 21:47 
AnswerRe: Another post with the same purpose PinmemberCyborgx3715-Jan-12 16:09 
AnswerRe: Another post with the same purpose PinmemberJerome Vibert18-May-12 23:15 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 14 Jan 2012
Article Copyright 2012 by Cyborgx37
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid