number base question (I think?)

I've been banging my head against this for a few hours, and have written a bunch of code that doesn't work.

I'm asking this in the math forum because I only want algorithmic advice... pseudo-code is fine.

I'm working in PHP with a class that generates excel files from stuff in a database. Context isn't important, but basically I need to convert a column number, such as 23, into the excel representation of that column (in this case W). This might sound very simple, but for numbers over 26, it gets very complicated. The function I write should be able to take 2358 as input, and return "CLR" as output, for example.

for those who aren't familiar, the excel numbers go like this:
A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC......BA,BB,BC....etc

any help figuring this out would be greatly appreciated

I understand how to convert from the letter version to the numbers, but forming the letter version in the first place is what confuses me... I feel like its very simple and I'm just being an idiot :blush:

For example BBA breaks down into (26*26*2)+(26*2)+(1) = 1405... but how do I go from 1405 to BBA?

I think I've figured it out now.

Lets start with 1405... divide 1405 by 26 until the result is less than 26.

1405 = (26^2)*2 + 53

The "*2" part is where we get B, so the first letter is B

Now do the same thing to 53 (our remainder)

53 = (26^1)*2 + 1

The multiplication part is 2 again, so we get a second B

1 = (26^0)*1 + 0

The multiplication part is 1 now, so the last letter is A, and there will be no more steps because the remainder is zero.

I was able to figure this out because the multiplication part is the only part that stays the same between step 1 and 2, and I knew that step 1 and 2 were the same letter, so something had to be the same... lol so I guess I worked it out ok. correct me if I'm wrong but I believe this is the correct approach

It sounds like your idea is correct, but I recommend doing checks with a few different column names to be sure.

I have posted a VB6 version of the code in the "Useful functions" section of my Excel Tutorial (link in my signature), but note that it only allows 2 letters for the column number - which could be extended using the same techniques.

Your confusion might come from the fact that the column names don't exactly use base 26. Setting A=1, B=2, ..., Z=26, there's no symbol for 0. However, you can artificially add a symbol for 0, say *. You'd then have to set Z = A* to make things make sense. You'd count like this:

0 == *
1 == A
2 == B
...
25 == Y
26 == Z = A*
27 == AA
28 == AB
...
(and so on)

Using the sequence on the right of those equalities exactly corresponds to counting in base 26, which is why your algorithm which basically is a base 26 converter works (aside from the edge cases involving *, which your algorithm seems to handle just fine).

yes that would explain the trouble I ran into when I finally got it to work with the exception of columns divisible by 26 (it would do AX, AY, BZ, BA). Instead of going back through and figuring out why it didn't work, I just added some conditionals that handled those cases separately. To avoid my own confusion when writing the function, I ended up dividing it up into two functions. here is the PHP code I ended up using

usage: $var = ToA1Final(2358);
in this case returns "CLR"

Code:

---

function ToA1Final($col)
{
        $orig = $col;
        $modify = false;
        if(floor($col / 26)==($col/26) && $col > 26)
        {
                $modify = true;
        }
        if($col==26)
        {
                return 'Z';
        }
        $tst = true;
        $count = 1;
        while($tst)
        {
                $olddiv = $col;
                $arr = IncrementStep($col);
                $col = $arr[1];
                if($col==$olddiv)
                {
                        $final = $final . GetLetter($arr[1]-1);
                        break;
                } else {
                        $final = $final . GetLetter($arr[0]-1);
                }
                $count++;
        }
        if($modify==true)
        {
                $txt = ToA1Final($orig - 1);
                $final = substr($txt, 0, strlen($txt)-1) . 'Z';
        }
        return $final;
}

function IncrementStep($div)
{
        $orig = $div;
        $tst = true;
        $count = 0;
        while($tst)
        {
                $count++;
                $div = floor($div / 26);
                if($div < 26)
                {
                        break;
                }
        }
        return array($div, $orig - (pow(26, $count) * $div));
}

function GetLetterIndex($let)
{
        $letters = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        return strpos($letters, $let) + 1;
}
function GetLetter($index)
{
        $letters='ABCDEFGHIJKLMNOPQRSTUVWXYZ';
        return substr($letters, $index, 1);
}

---

Obviously it could be simplified to one neat function, but for my purposes I'm all set. Could also be easily converted to VB.NET or [insert language here]. Doesn't use anything drastically language specific... just basic string manipulation, math, array manipulation, and dummy loops (it turns out PHP doesn't have a plain do loop procedure, so I did while([variable that will always be true]) and called break manually when I wanted to break to emulate this. I'm lazy :p