1

This question is based off this thread Programming Riddle: How might you translate an Excel column name to a number?

Here is code from that question to translate a column number to an excel column name

public String getColName (int colNum) {

   String res = "";
   int quot = colNum;
   int rem;        
    /*1. Subtract one from number.
    *2. Save the mod 26 value.
   *3. Divide the number by 26, save result.
   *4. Convert the remainder to a letter.
   *5. Repeat until the number is zero.
   *6. Return that bitch...
   */
    while(quot > 0)
    {
        quot = quot - 1;
        rem = quot % 26;
        quot = quot / 26;

        //cast to a char and add to the beginning of the string
        //add 97 to convert to the correct ascii number
        res = (char)(rem+97) + res;            
    }   
    return res;
}

I tested this code thoroughly and it works but I have a question about what this line needs to be repeated for this to work 

            quot = quot - 1;

From my understanding the quot is needed to map the col number to distance away from 'a'. That means 1 should map to 0 distance from 'a', 2 to 1 distance away from 'a' and so on. But don't you need to subtract this one once to account for this? Not in a loop I mean eventually,

            quot = quot / 26;

will stop the loop.

Community
  • 1
  • 1
committedandroider
  • 8,711
  • 14
  • 71
  • 126

1 Answers1

4

Excel columns aren't a normal number system. It's not just base 26. The first two-digit column is "AA". In any normal number system, the first two digit number is composed of two different digits. Basically, in excel column numbering, there is no "zero" digit.

To account for this difference, 1 is subtracted at each iteration.

recursive
  • 83,943
  • 34
  • 151
  • 241
  • I agree with the zero digit. That's why I understand if you pass in 26, subtract that by 1 to get 25, distance away from 'a'. But wouldn't that be all the times you need to do that. To me, you only have to account for this off by 1 once. Can you clarify this? – committedandroider Jan 23 '15 at 21:49
  • How do you represent a distance of 26 in excel columns? It's "AA". The first "A" contributes a non-zero distance, but the second "A" acts as a trailing "zero". In all places but the last, "A" means some positive value. Subtracting one corrects for this. – recursive Jan 23 '15 at 21:52
  • The way I see it was distance is in base 26. Seeing that 26/26 != 0, it means that this distance is going to take one more power of 26 to represent. That's why the quot = quot/26 makes sense to me. What doesn't make sense is in the next pass, quot will be equal to 1. If I have already taken care of the zero based excel numbering, why do i need to subtract 1 again? – committedandroider Jan 23 '15 at 21:55
  • The correct output for 27 is "aa". If you don't subtract 1 on the subsequent iteration, you will get "ab". In other words, you haven't "already taken care of the zero-based excel numbering". It must be "taken care of" on every iteration, and you're never done taking care of it until the loop is done. – recursive Jan 23 '15 at 21:59
  • I get that it's needed to produce the right output. I just don't get why it's needed on every iteration from intuition. – committedandroider Jan 23 '15 at 22:00
  • It's not a normal number system where each digit has a set value, so you can't expect to apply the standard base conversion algorithm. – recursive Jan 23 '15 at 22:03
  • So intuitively, when i do 26/26 and get 1, just remember this 1 also has its own set value in excel column naming? – committedandroider Jan 23 '15 at 22:05