# 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.

• Are you sure that 16383 should be XFD? What do you get for 676 and 702? – Peter Taylor Nov 24 '11 at 23:29
• Well, that's what Excel shows, and I found it on the web that it has 16384 columns. I'll test it tomorrow with our (known to work) code (is late night right now where I live). – Vilx- Nov 24 '11 at 23:56
• Also, testing with Excel itself reveals that 676=ZA and 702=AAA. – Vilx- Nov 24 '11 at 23:59
• 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. – Peter Taylor Nov 25 '11 at 8:21
• Yup. It's not Base-26. That's the problem. ;) – Vilx- Nov 25 '11 at 8:44

# 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! ## 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 – Vilx- Nov 25 '11 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 :) – Dr. belisarius Nov 26 '11 at 1:30 • I still might accept your answer. >:D – Vilx- Nov 26 '11 at 13:30 • <laughter type="evil">Muhahahahaha!</laughter> – Vilx- Nov 26 '11 at 22:31 • You can drop 2 bytes by replacing "1" with 1 – Taylor Scott Jul 12 '17 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. – Vilx- Sep 24 '12 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. – Ilmari Karonen Sep 24 '12 at 13:46 • Sorry how should this be run? With perl 5.18 it prints nothing when given as the argument to -E. – Ed Avis Jul 29 '15 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. – Ilmari Karonen Jul 29 '15 at 13:45 • I think this can be optimized to say+(A..XFD)[<>] – Konrad Borowski Oct 23 '18 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; }  ## Haskell, 48 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  ### 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 :) – quasimodo Sep 23 '12 at 15:02 • You could also shave 4 characters off by using f=lambda i: rather than def f(i):return – Strigoides Sep 25 '12 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 '' – user007 Oct 11 '17 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. – Peter Taylor Nov 25 '11 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. – Vilx- Oct 20 '18 at 21:34 • @Vilx- I take that as proof that You don´t know a lot PHP. I added a TiO; see for yourself. – Titus Oct 22 '18 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! – Vilx- Oct 22 '18 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? – AdmBorkBork Oct 17 '17 at 15:52 • @AdmBorkBork Not much to add to previous answers, just language bind! – LS_ᴅᴇᴠ Oct 17 '17 at 15:59 • This appears to fail on all cases where i>675 - s(676)=A@@(expected YZ), s(677)=A@A (expected ZA) – Taylor Scott Oct 19 '17 at 23:07 • @TaylorScott You're right. Working on it... – LS_ᴅᴇᴠ Oct 20 '17 at 8:07 • @TaylorScott Corrected, +6 bytes... Thanks. – LS_ᴅᴇᴠ Oct 20 '17 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. – Muhammad Salman May 1 '18 at 14:59 • Also answering a question asked 7 years ago is not really a great idea. – Muhammad Salman May 1 '18 at 15:01 • Ok , nvm this is wrong on so many levels how did I ever not see this – Muhammad Salman May 1 '18 at 15:08 • I wanted to ask this question but it was a duplicate. I'm not sure what you're getting at @MuhammadSalman – MattH May 1 '18 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 – Muhammad Salman May 1 '18 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. :) – Muhammad Salman May 1 '18 at 15:46 • @MuhammadSalman Hehe, no problem. I actually got mine from the Scala answer. ;) – Kevin Cruijssen May 1 '18 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 – Vilx- Apr 5 '15 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 '15 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!