Generate excel column name from index

This one comes from a real life problem. We solved it, of course, but it keeps feeling like it could have be done better, that it's too lengthy and roundabout solution. However none of my colleagues can think of a more succinct way of writing it. Hence I present it as code-golf.

The goal is to convert a nonnegative integer into a string the same way Excel presents its column headers. Thus:

0 -> A
1 -> B
...
25 -> Z
26 -> AA
27 -> AB
...
51 -> AZ
52 -> BA
...
16,383 -> XFD


It has to work at least up to 16,383, but beyond is acceptable too (no bonus points though). I'm looking forward most to the C# solution, but, as per traditions of code-golf, any real programming language is welcome.

• The reason I ask is that I wrote some straightforward base-26 code, got results which fit yours precisely, but broke on 676 and 702. Nov 25, 2011 at 8:21
• Yup. It's not Base-26. That's the problem. ;) Nov 25, 2011 at 8:44
• If your numbering started from 1 it would be base-27 (A-Z and the empty string representing the 0), I think. As it is, the rightmost column has 26 possible values and the rest have 27. Which is awkward. Nov 25, 2011 at 9:28
• This is a super old challenge, but I'm a bit flummoxed why you've specified a 0-based index, since Excel itself uses a 1-based index.=ADDRESS(1,1) returns $A$1. See Dr. belisarius' Excel answer. May 1, 2018 at 16:44
• @BradC - You know, I don't really remember anymore. :) It could be that I'm just used to 0-based indexing. Or maybe we did use that result as an index in a 0-based array (we used C#). Or maybe that's how it appears internally in the XLSX files. Anyways, converting from 0-based to 1-based is just a +1, so it doesn't matter much. :) May 1, 2018 at 17:26

Excel Formula:), 36 chars

=SUBSTITUTE(ADDRESS(1,A1,4),"1","")


Usage:

Sorry, couldn't resist ...

• Arghh! I had actually thought of prohibiting this, but forgot to mention it in the post! :D Still, Excel formulas are not a programming language (and yes, Excel VBA is off limits too). :P Nov 25, 2011 at 8:42
• @Vilx- Thanks God someone came up with a shorter solution. I don't want to enter history being the only person who won a golf contest using Excel formulas :) Nov 26, 2011 at 1:30
• I still might accept your answer. >:D Nov 26, 2011 at 13:30
• <laughter type="evil">Muhahahahaha!</laughter> Nov 26, 2011 at 22:31
• You can drop 2 bytes by replacing "1" with 1 Jul 12, 2017 at 9:47

Perl, 17 characters

say[A..XFD]->[<>]


The .. operator does the same thing as the magical auto-increment, but without the need for the temporary variable and loop. Unless strict subs is in scope, the barewords A and XFD are interpreted as strings.

(This answer was suggested by an anonymous user as an edit to an existing answer. I felt it deserves to be a separate answer, and have made it one. Since it wouldn't be fair for me to gain rep from it, I've made it Community Wiki.)

• Since it's the shortest answer so far, I guess it deserves to be marked as "accepted" until a shorter solution is found (probably only available in JonSkeetScript) :P Ironic. Sep 24, 2012 at 7:55
• Since the question is vague on how input and output are done, that actually allows shortening this considerably. For example, if input is in $_ and the output is the value of the expression, then (A..XFD)[$_] solves the challenge with only 12 chars. Sep 24, 2012 at 13:46
• Sorry how should this be run? With perl 5.18 it prints nothing when given as the argument to -E. Jul 29, 2015 at 13:33
• @EdAvis: It's waiting for you to type in a number. Or you could put the number in a file and do perl -E 'say[A..XFD]->[<>]' < number.txt. Or, in shells that support it, just give the input on the command line with perl -E 'say[A..XFD]->[<>]' <<< 123. Jul 29, 2015 at 13:45
• I think this can be optimized to say+(A..XFD)[<>] Oct 23, 2018 at 5:57

C, 53 characters

It's like playing golf with a hammer...

char b[4],*p=b+3;f(i){i<0||(*--p=i%26+65,f(i/26-1));}


Normal version:

char b[4];
char *p = b+3;
void f(int i) {
if (i >= 0) {
--p;
*p = i%26 + 65;
f(i/26-1);
}
}


And the usage is like that:

int main(int argc, char *argv[])
{
f(atoi(argv[1]));
printf("%s\n", p);
return 0;
}


f=(!!)(sequence=<<(tail$iterate(['A'..'Z']:)[]))  Less golfed: f n = (concatMap sequence$ tail $iterate (['A'..'Z'] :) []) !! n  Explanation Haskell's sequence combinator takes a list of actions and performs them, returning the result of each action in a list. For example: sequence [getChar, getChar, getChar]  is equivalent to: do a <- getChar b <- getChar c <- getChar return [a,b,c]  In Haskell, actions are treated like values, and are glued together using the >>= (bind) and return primitives. Any type can be an "action" if it implements these operators by having a Monad instance. Incidentally, the list type has a monad instance. For example: do a <- [1,2,3] b <- [4,5,6] return (a,b)  This equals [(1,4),(1,5),(1,6),(2,4),(2,5),(2,6),(3,4),(3,5),(3,6)] . Notice how the list comprehension is strikingly similar: [(a,b) | a <- [1,2,3], b <- [4,5,6]]  Because lists are a type of "action", we can use sequence with lists. The above can be expressed as: sequence [[1,2,3],[4,5,6]]  Thus, sequence gives us combinations for free! Thus, to build the list: ["A","B"..."Z","AA","AB"]  I just need to build lists to pass to sequence [['A'..'Z'],['A'..'Z','A'..'Z'],...]  Then use concatMap to both apply sequence to the lists, and concatenate the resulting lists. Coincidentally, concatMap is the =<< function for lists, so the list monad lets me shave a few characters here, too. Perl, 26 characters $x='A';map$x++,1..<>;say$x


Perl 6, 16 14 bytes

{("A"..*)[$_]}  Works even beyond XFD. Thanks to infinite lists in Perl 6, this doesn't take forever (and a half) to execute. Try it online! Ruby, 35 characters e=->n{a=?A;n.times{a.next!};a}  Usage: puts e[16383] # XFD  Note: There is also a shorter version (30 characters) using recursion.  e=->n{n<1??A:e[n-1].next}  But using this function you might have to increase the stack size for large numbers depending on your ruby interpreter. Groovy, 47 m={it<0?'':m(((int)it/26)-1)+('A'..'Z')[it%26]} [0:'A',1:'B',25:'Z', 26:'AA', 27:'AB', 51:'AZ', 52:'BA', 16383:'XFD'].collect {k,v-> assert v == m(k);m(k) }  Python 45 51 f=lambda i:i>=0and f(i/26-1)+chr(65+i%26)or''  • you can remove 2 parentheses by pulling +chr(65+i%26) inside and testing for i>=0, saving you 1 character :) Sep 23, 2012 at 15:02 • You could also shave 4 characters off by using f=lambda i: rather than def f(i):return Sep 25, 2012 at 3:39 • actually that doesn't work well for numbers 37 and above. I had to update this code a bit: f = lambda i: i >= 0 and f(math.floor(i / 26 - 1)) + chr(int(round(65 + i % 26))) or '' Oct 11, 2017 at 21:51 Scala, 62 characters def f(i:Int):String=if(i<0)""else f((i/26)-1)+(i%26+65).toChar  Usage: println(f(16383))  returns: XFD  You can try this on Simply scala. Copy and paste the function and use f(some integer) to see the result. • You don't need the ""+ on the else case. Nov 25, 2011 at 11:26 Excel VBA, 31 Bytes Anonymous VBE immediate window function that takes input from cell [A1] and outputs to the VBE immediate window ?Replace([Address(1,A1,4)],1,"")  JavaScript (Node.js), 50 bytes f=_=>_<0?'':f(_/26-1)+String.fromCharCode(_%26+65)  Try it online! Seeing that a lot of people started answering this I answered too. Note : This is basically a rip off of @kevinCruijssen's answer in Java shortened thanks to this being JS. PHP, 30 bytes for($c=A;$argn--;)$c++;echo$c;  Run as pipe with $$$$-nr' or try it online. • I'm pretty sure this doesn't do what is required. After Z it would go [ rather than AA. Oct 20, 2018 at 21:34 • @Vilx- I take that as proof that You don´t know a lot PHP. I added a TiO; see for yourself. Oct 22, 2018 at 8:47 • Holy... you're right! I do know PHP fairly well, but it's so full of weird stuff, that it's impossible to know it all. This particular oddity threw me off. Here, have an upvote and my apologies! Oct 22, 2018 at 9:28 VBA/VB6/VBScript (non-Excel), 73 bytes Function s(i):While i:i=i-1:s=Chr(i Mod 26+65)&s:i=i\26:Wend:End Function  Calling s(16383) will return XFC. • Welcome to PPCG! Can you add an explanation for users unfamiliar with VB? Oct 17, 2017 at 15:52 • @AdmBorkBork Not much to add to previous answers, just language bind! Oct 17, 2017 at 15:59 • This appears to fail on all cases where i>675 - s(676)=A@@(expected YZ), s(677)=A@A (expected ZA) Oct 19, 2017 at 23:07 • @TaylorScott You're right. Working on it... Oct 20, 2017 at 8:07 • @TaylorScott Corrected, +6 bytes... Thanks. Oct 20, 2017 at 8:25 Javascript, 147 bytes I had a similar problem. This is the golf of the solution. Excel columns are bijective base-26. n=>{f=Math.floor;m=Math.max;x=m(0,f((n-24)/676));y=m(0,f(n/26-x*26));return String.fromCharCode(...[x,y,n+1-x*676-y*26].filter(d=>d).map(d=>d+64))}  Expanded, except using 1-indices: function getColName(colNum){ // example: 16384 => "XFD" let mostSig = Math.max(0, Math.floor((colNum - 26 - 1)/26**2)); let midSig = Math.max(0, Math.floor((colNum - mostSig*26**2 - 1)/26)); let leastSig = colNum - mostSig*26**2 - midSig*26; return String.fromCharCode(...[mostSig,midSig,leastSig].filter(d=>d).map(d=>d+64)); }  • You could add a TIO link. Other than that a great first answer. Also welcome to PPCG. May 1, 2018 at 14:59 • Also answering a question asked 7 years ago is not really a great idea. May 1, 2018 at 15:01 • Ok , nvm this is wrong on so many levels how did I ever not see this May 1, 2018 at 15:08 • I wanted to ask this question but it was a duplicate. I'm not sure what you're getting at @MuhammadSalman May 1, 2018 at 15:11 • I will get back to you in a minute, Anyways welcome to PPCG. nice answer. Plz note that when writing an answer you must provide a full program or a function May 1, 2018 at 15:15 Java, 57 bytes (recursive) String f(int n){return n<0?"":f(n/26-1)+(char)(n%26+65);}  Try it online. Explanation: String f(int n){ // Recursive method with integer parameter and String return-type return n<0? // If n is negative: "" // Return an empty String : // Else: f(n/26-1) // Recursive call with n integer-divided by 26, minus 1 +(char)(n%26+65);} // And append n%26+65 as character  Java 10, 62 bytes (iterative) n->{var r="";for(;n>=0;n=n/26-1)r=(char)(n%26+65)+r;return r;}  Try it online. Explanation: n->{ // Method with integer parameter and String return-type var r=""; // Result-String, starting empty for(;n>=0; // Loop as long as n is not negative n=n/26-1) // After every iteration: divide n by 26, and subtract 1 r=(char)(n%26+65)+r; // Prepend n%26+65 as character to the result-String return r;} // Return the result-String  • Hi. Sorry but I stole your code : Here. :) May 1, 2018 at 15:46 • @MuhammadSalman Hehe, no problem. I actually got mine from the Scala answer. ;) May 1, 2018 at 20:05 Forth (gforth), 59 bytes : f dup 0< if drop else 26 /mod 1- recurse 65 + emit then ;  Try it online! Explanation dup 0< \ duplicate the top of the stack and check if negative if drop \ if negative, drop the top of the stack else \ otherwise 26 /mod \ divide by 26 and get the quotient and remainder 1- recurse \ subtract one from quotient and recurse on result 65 + emit \ add 65 to remainder and output ascii char then \ exit if statement  R, 65 bytes Recursive answer as are many previous answers. function(n,u=LETTERS[n%%26+1])"if"(n<=25,u,paste0(g(n%/%26-1),u))  Try it online! Powershell, 68 bytes param($n)for(;$n-ge0;$n=($n-$r)/26-1){$s=[char](($r=$n%26)+65)+$s}$s  Alternative recursive version, 68 bytes: filter g{if($_-ge0){(($_-($r=$_%26))/26-1|f)+[char]($r+65)}else{''}}


Test script:

$f = { param($n)for(;$n-ge0;$n=($n-$r)/26-1){$s=[char](($r=$n%26)+65)+$s}$s } filter g{if($_-ge0){(($_-($r=$_%26))/26-1|f)+[char]($r+65)}else{''}}

@(
,(0 , "A")
,(1 , "B")
,(25 , "Z")
,(26 , "AA")
,(27 , "AB")
,(51 , "AZ")
,(52 , "BA")
,(676 , "ZA")
,(702 , "AAA")
,(16383 , "XFD")
) | % {
$n,$expected = $_$result = &$f$n
# $result =$n|g      # Alternative
"$($result-eq$expected):$result"
}


Output:

True: A
True: B
True: Z
True: AA
True: AB
True: AZ
True: BA
True: ZA
True: AAA
True: XFD


Note: Powershell does not provide a div operator.

I really thought that I would be able beat the other Haskell entry, but alas...

f(-1)=""
f n=f(div n 26-1)++[toEnum$mod n 26+65]  I am certain it is possible to shave a couple of characters off this, but I haven't coded in Haskell for nearly a year, so I am quite rusty. It's not exactly what you would call elegant. • Not bad! :) But Ha - after more than 3 years, still no C# solution. :D Apr 5, 2015 at 18:20 • Haha, indeed. But a C# solution is trivial to write using this same method. string f(int n){return n<0?"":f(n/26-1)+(char)(n%26+65);} 57 characters, so I would almost feel bad by posting it as an answer. – Fors Apr 6, 2015 at 9:38 Jq 1.5, 71 bytes [range(1;4)as$l|[65+range(26)]|implode/""|combinations($l)]|map(add)[N]  Expects input in N. e.g. def N:16383;  Expanded: [ # create array with range(1;4) as$l     #  for each length 1,2,3
| [65+range(26)]       #   list of ordinal values A-Z
| implode/""           #   converted to list of strings ["A", "B", ...]
| combinations($l) # generate combinations of length$l
]
| map(add)[N]           # return specified element as a string


Try it online!

><>, 29 bytes

!v:2d*%:"A"+@-2d*,1-:0(?!
\$<o


Try it online!

Icon, 58 bytes

procedure f(n);return(n<0&"")|f(n/26-1)||char(65+n%26);end
`

Try it online!