Code Golf: Numeric equivalent of an Excel column name
The challenge
The shortest code by character count that will output the numeric equivalent of an Excel column string.
For example, the A
column is 1, B
is 2, so on and so forth. Once you hit Z
, the next column becomes AA
, then AB
and so on.
Test cases:
A: 1
B: 2
AD: 30
ABC: 731
WTF: 16074
ROFL: 326676
Code count includes input/output (i.e full program).
Excel, 9 chars :)
Use the right tool for the job:
=COLUMN()
Perl, 36 34 33 31 30 17 15 11 characters
$_=()=A..$_
Usage:
$ echo -n WTF | perl -ple '$_=()=A..$_'
16074
Reduced to 17 by using echo -n to avoid a chop
call.
Reduced to 15 by using say instead of print.
Reduced to 11 by using -p instead of say.
Explanation:
A
is evaluated in string context and A..$_
builds a list starting at "A" and string-incrementing up to the input string. Perl interprets the ++
operator (and thus ..
) on strings in an alphabetic context, so for example $_="AZ";$_++;print
outputs BA
.
=()=
(aka "goatse" operator) forces an expression to be evaluated in list context, and returns the number of elements returned by that expression i.e., $scalar = () = <expr>
corresponds to @list = <expr>; $scalar = @list
.
J, 17 12 10 characters
26#.64-~av
Example:
26#.64-~av 'WTF'
16074
Explanation:
- J parses from right to left.
av
returns a list of the ascii indexes of each of the characters in its argument, so for exampleav'ABC'
returns65 66 67
.- Then we subtract 64 from each element of that list with the verb
64-~
. - Then we convert the list to base 26 using the
#.
verb.
Brainf*ck, 81 characters (no whitespace)
,[>>>[->>+++++[-<+++++>]<+<]>[-<+>]<<++++++++[<++++++++>-]<[<->-]<[>>>+<<<-],]>>>
Explanation
,[ // get character input into p[0], enter loop if it isn't null (0)
>>>[->>+++++[-<+++++>]<+<] // take what's in p[3] and multiply by 26, storing it in p[4]
>[-<+>] // copy p[4] back to p[3]
<<++++++++[<++++++++>-]< // store 64 in p[1]
[<->-]< // subtract p[1], which is 64, from the input char to get it's alphabetical index
[>>>+<<<-] // add p[0] to p[3]
,] // get another character and repeat
>>> // move to p[3], where our final result is stored
So you'll notice I didn't actually convert the numerical value to an ascii string for printing. That would likely ruin the fun. But I did the favor of moving the pointer to the cell with the result, so at least it's useful to the machine.
Hey, what do you know, I beat C#!
Ruby 1.8.7, 53 50 46 44 24 17 characters
p ('A'..$_).count
Usage:
$ echo -n ROFL | ruby -n a.rb 326676 $ echo -n WTF | ruby -n a.rb 16074 $ echo -n A | ruby -n a.rb 1
APL
13 characters
Put the value in x
:
x←'WTF'
then compute it with:
26⊥(⎕aV⍳x)-65
The only reason J beat me is because of the parentheses. I'm thinking there should be some way to rearrange it to avoid the need for them, but it's been a long day. Ideas?
(Heh, you perl programmers with your 30+ character solutions are so cute!)
Excel (not cheating), 25 chars
Supports up to XFD:
=COLUMN(INDIRECT(A1&"1"))
Installation:
- Put the formula in cell A2.
Usage:
- Enter the column string in cell A1.
- Read the result at cell A2.
54 chars, plus a lot of instructions
Supports ROFL also:
(A2) =MAX(B:B)
(B2) =IFERROR(26*B1+CODE(MID(A$1,ROW()-1,1))-64,0)
Installation:
- Clear the whole spreadsheet.
- Put the formula (A2) in cell A2.
- Put the formula (B2) in cell B2.
- Fill formula (B2) to as far down as possible.
Usage:
- Enter the column string in cell A1.
- Read the result at cell A2.
C# 156 146 118 Chars
using System.Linq;class P{static void Main(string[]a){System.Console.Write(
a[0].Aggregate(0,(t,c)=>(t+c-64)*26)/26);}}
Ungolfed:
using System.Linq;
class P
{
static void Main(string[] a)
{
System.Console.Write(a[0]
.Aggregate(0, (t, c) => (t + c - 64) * 26) / 26);
}
}
Golfscript - 16 chars
[0]\+{31&\26*+}*
$ echo -n WTF | ./golfscript.rb excel.gs
16074
$ echo -n ROFL | ./golfscript.rb excel.gs
326676
Haskell, 50 51 56 chars
main=interact$show.foldl(\x->(26*x-64+).fromEnum)0
Usage:
~:166$ echo -n "ROFL" | ./a.out
326676
~:167$ echo -n "WTF" | ./a.out
16074
Python, 64 49 characters
s=0
for c in raw_input():s=26*s+ord(c)-64
print s
You can also replace raw_input()
with input()
to reduce the character count by 4, but that then requires the input to contain quotation marks around it.
And here's a subroutine that clocks in at 47 characters:
f=lambda x:len(x)and 26*f(x[:-1])+ord(x[-1])-64
k4 (kdb+), 11 characters
26/:1+.Q.A?
Explanation:
- k4 parses left of right
.Q.A
is defined within k4 - it is the vector"ABC...XYZ"
?
is the find operator - the index of the first match for items in the y arg within the x arg- +1 to offset the index
26/:
to convert to base 26
One caveat - this will only work where listed types are passed in:
26/:1+.Q.A? "AD"
30
26/:1+.Q.A? "WTF"
16074
but:
26/:1+.Q.A? ,"A"
1
Powershell, 42 chars
[char[]]$args[($s=0)]|%{$s=$s*26+$_-64};$s
JavaScript 1.8: 66 characters
function a(p)Array.reduce(p,function(t,d)t*26+d.charCodeAt()-64,0)
Javascript 1.8: 72 characters
function a(p)(t=0,p.replace(/./g,function(d)t=t*26+d.charCodeAt()-64),t)
JavaScript 1.6: 83 characters
function a(p){t=0;p.split("").map(function(d){t=t*26+d.charCodeAt(0)-64});return t}
JavaScript: 95 characters
function a(p){r=0;t=1;l=p.length;for(i=0;i<l;i++){r+=(p.charCodeAt(l-1-i)-64)*t;t*=26}return r}
JavaScript: 105 characters
function a(p,i){i=i||0;l=p.length;return p?(p.charCodeAt(l-1)-64)*Math.pow(26,i)+a(p.slice(0,l-1),i+1):0}
Usage:
a("A") // 1
a("B") // 2
a("AD") // 30
a("ABC") // 731
a("WTF") // 16074
a("ROFL") // 326676
Scala, 30 chars
print((0/:args(0))(_*26+_-64))"
Example:
C:\>scala -e "print((0/:args(0))(_*26+_-64))" AD
30
C89, 58 characters
s;main(c){while(c=getchar()+1)s=26*s+c-65;printf("%d",s);}
The input (stdin) must contain only A-Z, no other characters (including newlines) are allowed.
Explanation of Concepts - Excelcification
Nice. I wrote my own version of this with a little more explanation a long time ago at http://aboutdev.wordpress.com/2009/12/19/excelcification-brain-teaser-code/. Although it's not quite an optimized version!
FYI. The base 26 arithmetic is called hexavigesimal and Excel's maximum column is XFD which converts to 16383 (using 0 as the first cell) which is coincidentally exactly 2^14 cells.
Can anyone guess as to why it is 2^14??
Common Lisp, 103 128 characters
(defun x(s)(reduce(lambda(x y)(+(* 26 x)y))(map 'vector(lambda(b)(-(char-code b)(char-code #\A)-1))s)))
C#, 117 111 chars
No contest compared to the likes of Perl, Ruby and APL but an improvement on the other C#/Java answers given so far.
This uses Horner's rule.
class C{static void Main(string[]a){int t=0;foreach(var c in a[0]){t=(t+c-64)*26;}System.Console.Write(t/26);}}
Perl, 34 characters
map$\=26*$\-64+ord,pop=~/./g;print
Thanks to mobrule for several suggestions.
C#, 148 chars
using System;class P{static void Main(string[]a){var r=0d;int j=0,i=a[0].
Length;while(i-->0)r+=(a[0][i]-64)*Math.Pow(26,j++);Console.WriteLine(r);}}
Ungolfed:
using System;
class P
{
static void Main(string[] a)
{
var r = 0d;
int j = 0, i = a[0].Length;
while (i-- > 0)
r += (a[0][i] - 64) * Math.Pow(26, j++);
Console.WriteLine(r);
}
}
Python - 63 chars
>>> f=lambda z: reduce(lambda x,y: 26*x+y, [ord(c)-64 for c in z])
>>> f('ROFL')
326676
Clojure:
user> (reduce #(+ (* 26 %1) %2) (map #(- (int %) 64) "AD"))
30
user> (reduce #(+ (* 26 %1) %2) (map #(- (int %) 64) "ROFL"))
326676
51 characters, plus the number of characters in the input string.
C:
int r=0;
while(*c)r=r*26+*c++-64;
String is stored in 'c', value is in 'r'.
Ruby 1.9, 21 characters
p'A'.upto(gets).count
Tests:
$ echo -n A| ruby x.rb
1
$ echo -n WTF| ruby x.rb
16074
$ echo -n ROFL| ruby x.rb
326676
Common Lisp, 86 characters.
(defun z(s)(let((a 0))(map nil(lambda(v)(setf a(+(* 26 a)(digit-char-p v 36)-9)))s)a))
Java: 112 124 characters
class C{public static void main(String[]a){int r=0;for(int b:a[0].getBytes())r=26*r+b-64;System.out.print(r);}}
Common Lisp, 81 characters
(defun y(s)(reduce(lambda(x y)(+(* 26 x)(-(char-code y)64)))s :initial-value 0))
Funny that as a new user I can post my own answer but not comment on someone else's. Oh well, apologies if I'm doing this wrong!
MATLAB: 24 characters
polyval(input('')-64,26)
Usage:
>> polyval(input('')-64,26)
(after pressing enter) 'WTF'
ans =
16074
Note: You can get it down to 16 characters if you pre-store the string in x
, but I kind of thought it was cheating:
>> x = 'WTF'
x =
WTF
>> polyval(x-64,26)
ans =
16074
PHP - 73 Chars
$n=$argv[1];$s=$i=0;while($i<strlen($n))$s=$s*26+ord($n[$i++])-64;echo$s;
Usage:
php -r '$n=$argv[1];$s=$i=0;while($i<strlen($n))$s=$s*26+ord($n[$i++])-64;echo$s;' AA
> 27
精彩评论