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.
A: 1
B: 2
AD: 30
ABC: 731
WTF: 16074
ROFL: 326676
Code count includes input/output (i.e full program).
Use the right tool for the job:
=COLUMN()
=COL()
. 6 characters. (See dolf.trieschnigg.nl/excel/excel.html ) - Debilski
ROFL
though. - YOU
$_=()=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
[1]) 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
.
$_
or $` instead of
$n` and call print
with no args. - mob
print
to say
and drop another 2 characters :) - mpeters
-n
if you use -l
here. - jfs
26#.64-~av
Example:
26#.64-~av 'WTF'
16074
Explanation:
av
returns a list of the ascii indexes of each of the characters in its argument, so for example av'ABC'
returns 65 66 67
.64-~
.#.
verb.,[>>>[->>+++++[-<+++++>]<+<]>[-<+>]<<++++++++[<++++++++>-]<[<->-]<[>>>+<<<-],]>>>
,[ // 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#!
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
p (
and p(
is different, :( - YOU
echo -n ...
so you can drop the .chop
- John La Rooy
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!)
Supports up to XFD:
=COLUMN(INDIRECT(A1&"1"))
Installation:
Usage:
Supports ROFL also:
(A2) =MAX(B:B)
(B2) =IFERROR(26*B1+CODE(MID(A$1,ROW()-1,1))-64,0)
Installation:
Usage:
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);
}
}
Console.Write
instead of WriteLine
? - Andreas Grech
using
keyword. - erikkallen
[0]\+{31&\26*+}*
$ echo -n WTF | ./golfscript.rb excel.gs
16074
$ echo -n ROFL | ./golfscript.rb excel.gs
326676
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
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
f=lambda x:len(x)and 26*f(x[:-1])+ord(x[-1])-64
- Ponkadoodle
26/:1+.Q.A?
Explanation:
.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 arg26/:
to convert to base 26One 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
function a(p)Array.reduce(p,function(t,d)t*26+d.charCodeAt()-64,0)
function a(p)(t=0,p.replace(/./g,function(d)t=t*26+d.charCodeAt()-64),t)
function a(p){t=0;p.split("").map(function(d){t=t*26+d.charCodeAt(0)-64});return t}
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}
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
(t?g*26:0)
part. - David Murdoch
string#replace
- Chetan S
[char[]]$args[($s=0)]|%{$s=$s*26+$_-64};$s
Scala, 30 chars
print((0/:args(0))(_*26+_-64))"
Example:
C:\>scala -e "print((0/:args(0))(_*26+_-64))" AD
30
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.
getchar()
returns < 0
on EOF; EOF is not defined as -1, but it's common. - strager
c=getchar()+1
to (c=getchar())>=0
and 65
to 64
). But, this should work in almost any C implementation. - Adam Rosenfield
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 [1] 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??
[1] http://en.wikipedia.org/wiki/Hexavigesimal26^n
for position n
(with n = 0
for the rightmost letter), it all works out as usual. - Thomas
(defun x(s)(reduce(lambda(x y)(+(* 26 x)y))(map 'vector(lambda(b)(-(char-code b)(char-code #\A)-1))s)))
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 [1].
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);}}
[1] http://en.wikipedia.org/wiki/Horner%27s_rulemap$\=26*$\-64+ord,pop=~/./g;print
Thanks to mobrule for several suggestions.
pop=~/./g
instead of split//,$ARGV[0]
You can omit the $_
in the ord
call. You can use $` instead of
$s` and then just say print
. - mob
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
echo A |
, it should be echo A|
- YOU
echo -n
like others and get rid of chop
. - Daniel C. Sobral
echo -n
is not working on Windows. - YOU
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
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))
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'.
in VBA I got it down to 98
Sub G(s)
Dim i, t
For i = 0 To Len(s) - 1
t = t + ((Asc(Left(Right(s, i + 1), 1)) - 64)) * ((26 ^ i))
Next
MsgBox t
End Sub
p('A'..$*[0]).count
Usage:
$ ruby a.rb ABC
731
$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
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!
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
chomp($n=<>);@c=split(//,uc($n));$o=64;$b=0;$l=$#c;for($i=$l;$i>=0;$i--){$b+=((26**($l-$i))*(ord($c[$i])-$o));}print$b;
Usage:
vivin@serenity ~/Projects/code/perl/excelc
$ echo WTF | perl e.pl
16074
vivin@serenity ~/Projects/code/perl/excelc
$ echo ROFL | perl e.pl
326676
I'm sure some of the Perl gurus here can come up with something way smaller.
chop($l=<>);$_=A;$.++,$_++while$_ ne$l;die$.,$/
++
operator does on strings -- see perldoc perlop
and search "little extra builtin magic". I'll write a more thorough explanation after I take care of some business at work. - hobbs
with(prompt())for(l=length,i=0,v=i--;++i<l;)v+=(charCodeAt(l-1-i)-64)*Math.pow(26,i);alert(v)
with
statement I have ever seen. (-: - Na7coldwater
x=0 for c in(...):gfind(".")do x=x*26-64+c:byte()end print(x)
wazoox:
echo -n WTF | perl -ple '$=()=A..$'
This prints a new line so the answer is more readable on the shell.
Smalltalk arguments first reverse inject:0into:[:o :e|o*26+e digitValue]
PHP: 56 55 characters
for($i='a';$i++!=strtolower($argv[1]);@$c++){}echo++$c;
PHP: 44 43 characters only for uppercase letters
for($i='A';$i++!=$argv[1];@$c++){}echo++$c;
Applescript: 188
Here's the requisite applescript in 188 characters, which is a very difficult language to make non-verbose. It also happens to be the longest answer of any language so far. If anyone knows how to shorten it, do share.
on run s set {o, c} to {0, 0} repeat with i in reverse of (s's item 1)'s characters set m to 26 ^ c as integer set c to c + 1 set o to o + ((ASCII number of i) - 64) * m end repeat end run
Usage:
osascript /path/to/script.scpt ROFL
for($a=A;++$c,$a++!=$argv[1];);echo$c;
usage, e.g.
php -r 'for($a=A;++$c,$a++!=$argv[1];);echo$c;' WTF
Store desired string in variable w:
w←'rofl'
Assuming characters are lowercase:
26⊥⎕a⍳w
Assuming characters are uppercase:
26⊥⎕A⍳w
Mixed case or unsure of case (14 chars, but could possibly be improved):
26⊥⊃⌊/⎕a⎕A⍳¨⊂w
Python
import string
letters = string.uppercase
colnum = lambda s: sum((letters.index(let)+1)*26**idx for idx, let in enumerate(s[::-1]))
print colnum('WTF')
# 16074
print colnum('ROFL')
# 326676
public class A{public static void main(String[] z){int o=0,c=0;for(int i=z[0].length()-1;i>=0;i--,c++)o+=(z[0].charAt(i)-64)*Math.pow(26,c);System.out.println(o);}}
public class A
{
public static void main(String[] z)
{
int m,o=0,c=0;
for(int i=z[0].length()-1;i>=0;i--,c++)
{
m=(int)Math.pow(26,c);
o+=(z[0].charAt(i)-64)*m;
}
System.out.println(o);
}
}
Assumes an uppercase input (via command line argument). The obvious approach with no tricks.
(does the opposite)
dc
can't handle character input, so I coded the opposite: input the column number and output the column name:
?[26~64+rd0<LP]dsLxP
dc exccol.dc 326676 ROFL
36i
. - Adam Rosenfield
My Javascript solution is just 82 characters long and uses Integer.parseInt with Radix 36. It'd be fine if somebody could appen this to the Javascript section of this thread! :-)
a=function(b){t=0;b.split('').map(function(n){t=parseInt(n,36)-9+t*26});return t};
PHP:
<?$t=0;$s=str_split($argv[1]);$z=count($s);foreach($s as$v){$z--;$t+=(ord($v)-64)*pow(26,$z);}echo$t?>
usage: php filename.php ROFL
outputs: 326676
reduce(lambda a,b:a*26+ord(b)-64,raw_input(),0)
works only on uppercase letters
Matlab 38 chars
Works only with uppercase letters. Not sure if it has to work with lowercase too (none in example).
x=input('')'-64;26.^(size(x)-1:-1:0)*x
If new lines do not count only 37 (omitting semicolon):
x=input('')'-64
26.^(size(x)-1:-1:0)*x
I see Matlab beats a lot of languages. Who would expect that.
Example:
Input: 'ROFL' (dont forget the '' )
Output: ans = 326676
Factor: 47 characters
reverse [ 26 swap ^ swap 64 - * ] map-index sum
Prolog: 49 chars
c([],A,A). c([H|T],I,R):-J is H-64+I*26,c(T,J,R).
Using the above code:
| ?- c("WTF",0,R). R = 16074 ? yes | ?- c("ROFL",0,R). R = 326676 ? yes
php 29 chars:
while($i++!=$t)$c++;echo$c+1;
using list comprehensions:
s=input()
print sum([((26**(len(s)-i-1))*(ord(s[i])-64)) for i in range(len(s))])
Josl in 48 characters
main 0 0 argv each 64 - swap 26 * + next print
Examples:
$ josl numequiv.j A
1
$ josl numequiv.j ABC
731
$ josl numequiv.j ROFL
326676
Reading from standard input:
main 0 STDIN read-line each 64 - swap 26 * + next print
OOBasic: 178 characters, not counting indentational whitespace
This version passes all the test cases. I suspect that it would be more successfully golf if it didn't "take advantage" of the fact that there's a spreadsheet using this numbering system. See the notes on the original version below for info on why that's not particularly useful. I didn't try very hard to cut down the score.
Also note that this will only work when run as a macro from an OO calc spreadsheet, for obvious reasons.
Function C(st as String) as Long
C = 0
while len(st)
C = C*26 + ThisComponent.Sheets(0).getCellRangeByName(left(st,1) &"1").CellAddress.Column+1
st = mid(st,2)
wend
End Function
OOBasic (OpenOffice Basic), too many characters (124):
Function C(co As String) As Long
C = ThisComponent.Sheets(0).getCellRangeByName(co &"1").CellAddress.Column+1
End Function
Limitations:
Notes:
Anyway entering =C("A")
, =C("ABC")
, etc. in a cell works for the first four test cases; the last two give errors.
filter: 97 chars
{ read c;i=0;while [ $c ];do eval s=({A..${c:0:1}});i=$((i*26+${#s[@]}));c=${c:1};done;echo $i;}
Usage:
echo ROFL | { read c;i=0;while [ $c ];do eval s=({A..${c:0:1}});i=$((i*26+${#s[@]}));c=${c:1};done;echo $i;}
326676
function: 98 chars
C(){ i=0;while [ $1 ];do eval s=({A..${1:0:1}});i=$((i*26+${#s[@]}));set -- ${1:1};done;echo $i;}
Usage:
C ROFL
326676
Explanation of the filter version:
read c;i=0;
Initialize the column and the total.
while [ $c ];do
while there are still column characters left
eval s=({A..${c:0:1}});
${c:0:1}
returns the first character of the column; s=({A..Z})
makes s an array containing the letters from A to Z
i=$((i*26+${#s[@]}));
$((...))
wraps an arithmetic evaluation; ${#s[@]}
is the number of elements in the array $s
c=${c:1};done;
${c:1}
is the characters in $c after the first. done
ends the while loop
echo $i
um i forget
better but dubious
Removing the 5 characters "echo " will result in the output for an input of "ROFL" being
326676: command not found
Also the i=0
is probably not necessary if you're sure that you don't have that variable set in your current shell.
F# (37 chars):
Seq.fold (fun n c -> int c-64+26*n) 0
26_sv -64+_ic
Usage:
26_sv -64+_ic"ROFL"
326676
Explanation:
range("WTF").Column
p ("A"..$*[0]).to_a.size
I fail at real golf too.
Private Sub CB1_Click()
Dim C, S
Range("A1").Select
Do
S = Len(ActiveCell)
x = 0
C = 0
Do
C = (Asc(Mid(ActiveCell, (S - x), 1)) - 64) * (26 ^ x) + C
x = x + 1
Loop Until x = S
ActiveCell.Offset(0, 1) = C
ActiveCell.Offset(1, 0).Activate
Loop Until ActiveCell = ""
End Sub
Uses Column A for input, outputs to Column B, runs off a VB command button click. =D
Elang, 53/78
Shell, 53 characters:
F=fun(S)->lists:foldl(fun(C,A)->A*26+C-64end,0,S)end.
Module, 78 characters:
-module(g).
-export([f/1]).
f(S)->lists:foldl(fun(C,A)->A*26+C-64end,0,S).
let e2n (c : string) = c |> Seq.map (fun x -> (int)x - 64) |> Seq.reduce(fun e a -> a*26+e)
Groovy: 51 Characters
char[] a=args[0];t=0;for(i in a)t=26*t+i-64;print t
Invoke as
groovy *scriptname* ROFL
or
groovy -e "char[] a=args[0];t=0;for(i in a)t=26*t+i-64;print t" ROFL
This essentially the same as Java. I imagine some possibilities with using ranges and closures, but nothing came to mind for this example. Anyone else see a way to shorten this?
A more groovy-looking version with a closure is a bit longer, unfortunately.
t=0;args[0].toCharArray().each{t=t*26+it-64};print t
Go: 106 characters
It's not the shortest of all the languages. But it can be the shortest of C, C++, Java, and C#.
package main
import("os"
"fmt")
func main(){t:=0
for _,c := range os.Args[1]{t=t*26+c-64}
fmt.Println(t)}
Formated version:
package main
import (
"os"
"fmt"
)
func main() {
t := 0
for _, c := range os.Args[1] {
t = t*26 + c - 64
}
fmt.Println(t)
}
Excel - 99 characters
Enter as array formula - I am not counting Excel adding { }
=SUM((CODE(MID(A1,ROW(INDIRECT("1:" & LEN(A1))),1))-64)*26^(LEN(A1)-ROW(INDIRECT("1:" & LEN(A1)))))
how about a new language
with operators defined as
# - will return the =COLUMN() of EXCEL as a stringised number
, - read in a string
. - write out a string
then the program to do that is
,#.