# 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

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)[<>]
– null
Oct 23, 2018 at 5:57

# 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 # 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 # 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. ## Excel (MS365), 32 bytes. =TEXTBEFORE(ADDRESS(1,A1,4),"1") • Welcome to Code Golf! Oct 22, 2022 at 15:38 • @RadvylfPrograms Sir, gracias . Oct 22, 2022 at 15:39 ## 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,"") # 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! # 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!

# 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

# 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

# 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. # Ruby, 26 bytes ->n{(?A..).take(n+1).last} Attempt This Online! # Excel, 30 bytes =@TEXTSPLIT(ADDRESS(1,A1,4),1) # Haskell, 48 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

# ><>, 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!

# Factor + successor, 27 bytes

[ "A"[ successor ] repeat ]

Attempt This Online!

Yup, Factor has a vocab for this.

• Still not as crazy as mathematica Oct 24, 2022 at 1:32

# C (GCC), 39 bytes

f(i){i>25&&f(i/26-1);putchar(65+i%26);}

Try It Online!

Explanation:

f(i)
{
// If i cannot be represented as a single letter
i>25 &&
// Print the next letter
f(i/26-1);

// Print the current letter
putchar(65+i%26);
}

I thought using 0-based indexing was a little strange, so here's 1-based indexing in the same number of bytes:

f(i){i-->26&&f(i/26);putchar(65+i%26);}

Try It Online!