Generate excel column name from index

Question

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.

Answer 1

Excel Formula:), 36 chars

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

Usage:

Sorry, couldn't resist ...

Answer 2

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

Answer 3

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.

Answer 4

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;
}

Answer 5

Perl, 26 characters

$x='A';map$x++,1..<>;say$x

Answer 6

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!

Answer 7

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.

Answer 8

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) }

Answer 9

Python 45 51

f=lambda i:i>=0and f(i/26-1)+chr(65+i%26)or''

Answer 10

PHP, 30 bytes

for($c=A;$argn--;)$c++;echo$c;

Run as pipe with `-nr' or try it online.

Answer 11

Excel (MS365), 32 bytes.

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

Answer 12

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.

Answer 13

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,"")

Answer 14

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

Answer 15

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.

Answer 16

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!

Answer 17

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.

Answer 18

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));
}

Answer 19

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

Answer 20

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!

Answer 21

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.

Answer 22

Ruby, 26 bytes

->n{(?A..).take(n+1).last}

Attempt This Online!

Answer 23

Excel, 30 bytes

=@TEXTSPLIT(ADDRESS(1,A1,4),1)

Answer 24

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.

Answer 25

><>, 29 bytes

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

Try it online!

Answer 26

Icon, 58 bytes

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

Try it online!

Answer 27

Factor + successor, 27 bytes

[ "A"[ successor ] repeat ]

Attempt This Online!

Yup, Factor has a vocab for this.

Answer 28

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!