Convert decimal number to excel-header-like number

Question

0 = A
1 = B
...
25 = Z
26 = AA
27 = AB
...
701 = ZZ
702 = AAA

I cannot think of any solution that does not involve loop-bruteforce :-(

I expect a function/program, that accepts a decimal number and returns a string as a result.

Answer 1

Haskell, 78 57 50 43 chars

o=map(['A'..'Z']:)$[]:o
e=(!!)$o>>=sequence

Other entries aren't counting the driver, which adds another 40 chars:

main=interact$unlines.map(e.read).lines

A new approach, using a lazy, infinite list, and the power of Monads! And besides, using sequence makes me :), using infinite lists makes me :o

Answer 2

If you look carefully the excel representation is like base 26 number but not exactly same as base 26.

In Excel conversion Z + 1 = AA while in base-26 Z + 1 = BA

The algorithm is almost same as decimal to base-26 conversion with just once change. In base-26, we do a recursive call by passing it the quotient, but here we pass it quotient-1:

function decimalToExcel(num)

        // base condition of recursion.
        if num < 26
                print 'A' + num 

        else                     
                quotient = num / 26;
                reminder = num % 26;

                // recursive calls.
                decimalToExcel(quotient - 1);
                decimalToExcel(reminder);
        end-if                       
end-function 

Java Implementation

Answer 3

Python, 44 chars

Oh c'mon, we can do better than lengths of 100+ :

X=lambda n:~n and X(n/26-1)+chr(65+n%26)or''

Testing:

>>> for i in 0, 1, 25, 26, 27, 700, 701, 702:
...     print i,'=',X(i)
...     
0 = A
1 = B
25 = Z
26 = AA
27 = AB
700 = ZY
701 = ZZ
702 = AAA

Answer 4

Since I am not sure what base you're converting from and what base you want (your title suggests one and your question the opposite), I'll cover both.

Algorithm for converting ZZ to 701

First recognize that we have a number encoded in base 26, where the "digits" are A..Z. Set a counter a to zero and start reading the number at the rightmost (least significant digit). Progressing from right to left, read each number and convert its "digit" to a decimal number. Multiply this by 26^a and add this to the result. Increment a and process the next digit.

Algorithm for converting 701 to ZZ

We simply factor the number into powers of 26, much like we do when converting to binary. Simply take num%26, convert it to A..Z "digits" and append to the converted number (assuming it's a string), then integer-divide your number. Repeat until num is zero. After this, reverse the converted number string to have the most significant bit first.

Edit: As you point out, once two-digit numbers are reached we actually have base 27 for all non-least-significant bits. Simply apply the same algorithms here, incrementing any "constants" by one. Should work, but I haven't tried it myself.

Re-edit: For the ZZ->701 case, don't increment the base exponent. Do however keep in mind that A no longer is 0 (but 1) and so forth.

Explanation of why this is not a base 26 conversion

Let's start by looking at the real base 26 positional system. (Rather, look as base 4 since it's less numbers). The following is true (assuming A = 0):

 A = AA = A * 4^1 + A * 4^0 = 0 * 4^1 + 0 * 4^0 = 0
 B = AB = A * 4^1 + B * 4^0 = 0 * 4^1 + 1 * 4^0 = 1
 C = AC = A * 4^1 + C * 4^0 = 0 * 4^1 + 2 * 4^0 = 2
 D = AD = A * 4^1 + D * 4^0 = 0 * 4^1 + 3 * 4^0 = 3
     BA = B * 4^0 + A * 4^0 = 1 * 4^1 + 0 * 4^0 = 4

And so forth... notice that AA is 0 rather than 4 as it would be in Excel notation. Hence, Excel notation is not base 26.

Answer 5

In Excel VBA ... the obvious choice :)

Sub a()

  For Each O In Range("A1:AA1")
    k = O.Address()
    Debug.Print Mid(k, 2, Len(k) - 3); "="; O.Column - 1
  Next

End Sub

Or for getting the column number in the first row of the WorkSheet (which make more sense, since we are in Excel ...)

Sub a()

  For Each O In Range("A1:AA1")
    O.Value = O.Column - 1
  Next

End Sub

Or better yet:

56 chars

Sub a()
  Set O = Range("A1:AA1")
  O.Formula = "=Column()"
End Sub

Answer 6

Scala: 63 chars

def c(n:Int):String=(if(n<26)""else c(n/26-1))+(65+n%26).toChar

Answer 7

Prolog, 109 123 bytes

Convert from decimal number to Excel string:

c(D,E):- d(D,X),atom_codes(E,X).
d(D,[E]):-D<26,E is D+65,!.
d(D,[O|M]):-N is D//27,d(N,M),O is 65+D rem 26.

That code does not work for c(27, N), which yields N='BB'

This one works fine:

c(D,E):-c(D,26,[],X),atom_codes(E,X).
c(D,B,T,M):-(D<B->M-S=[O|T]-B;(S=26,N is D//S,c(N,27,[O|T],M))),O is 91-S+D rem B,!.

Tests:

?- c(0, N).
N = 'A'.


?- c(27, N).
N = 'AB'.

?- c(701, N).
N = 'ZZ'.

?- c(702, N).
N = 'AAA'

Converts from Excel string to decimal number (87 bytes):

x(E,D):-x(E,0,D).
x([C],X,N):-N is X+C-65,!.
x([C|T],X,N):-Y is (X+C-64)*26,x(T,Y,N).

Answer 8

F# : 166 137

let rec c x  = if x < 26 then [(char) ((int 'A') + x)] else List.append (c (x/26-1)) (c (x%26))
let s x = new string (c x |> List.toArray)

Answer 9

PHP: At least 59 and 33 characters.

<?for($a=NUM+1;$a>=1;$a=$a/26)$c=chr(--$a%26+65).$c;echo$c;

Or the shortest version:

<?for($a=A;$i++<NUM;++$a);echo$a;

Answer 10

Using the following formula, you can figure out the last character in the string:

transform(int num)
    return (char)num + 47; // Transform int to ascii alphabetic char. 47 might not be right.

char lastChar(int num)
{
    return transform(num % 26);
}

Using this, we can make a recursive function (I don't think its brute force).

string getExcelHeader(int decimal)
{
    if (decimal > 26)
        return getExcelHeader(decimal / 26) + transform(decimal % 26);
    else
        return transform(decimal);
}

Or.. something like that. I'm really tired, maybe I should stop answering questions and go to bed :P