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 example av'ABC' returns 65 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:

2. Put the formula in cell A2.

Usage:

1. Enter the column string in cell A1.
2. 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:

1. Clear the whole spreadsheet.
2. Put the formula (A2) in cell A2.
3. Put the formula (B2) in cell B2.
4. Fill formula (B2) to as far down as possible.

Usage:

4. Enter the column string in cell A1.
5. 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

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

Powershell, 42 chars

[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

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.

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

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

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

Ruby, 20 characters

p('A'..$*[0]).count

Usage:

$ ruby a.rb ABC
731

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

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

Perl, 120 characters

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.

Perl, 47 characters (from stdin)

chop($l=<>);$_=A;$.++,$_++while$_ ne$l;die$.,$/

JavaScript, 93 characters

with(prompt())for(l=length,i=0,v=i--;++i<l;)v+=(charCodeAt(l-1-i)-64)*Math.pow(26,i);alert(v)

Lua, 61 characters

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, 72

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

PHP, 38 chars

for($a=A;++$c,$a++!=$argv[1];);echo$c;

usage, e.g.

php -r 'for($a=A;++$c,$a++!=$argv[1];);echo$c;' WTF

APL: 7 characters

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

Java, 164 characters

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

Java, 177 characters

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.

dc - 20 chars

(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

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

Python (47 chars)

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;

• assuming register_globals=On
• assuming error_reporting=0
• call via webserver ?i=A&t=ABC

Python: 88 characters

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

revised

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

original

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:

• maximum value of co is AMJ (1024 columns). Anything larger results in an error with a completely uninformative error message.
  • This limitation is also present for the COLUMN() cell function. Presumably this is the maximum number of columns in an OOCalc spreadsheet; I didn't bother scrolling over that far or googling to find out.

Notes:

• strangely it's not possible to give the variable 'co' a 1-letter name. Not sure what the logic is behind this, but after having spent enough time using OOBasic you stop looking for logic and begin to blindly accept the way things are (perhaps from gazing too long at the Sun).

Anyway entering =C("A"), =C("ABC"), etc. in a cell works for the first four test cases; the last two give errors.

straight bash

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

K 3.2 (13 characters)

26_sv -64+_ic

Usage:

  26_sv -64+_ic"ROFL"
326676

Explanation:

• As mentioned above K evaluates from right to left, so the _ic function takes whatever is to its right and converts it to an integer value, this includes both single characters and character vectors
• -64 is added to each item in the integer vector that to get a set of base values
• _sv takes two arguments: the one on its left is the numeric base, 26, and the one on its right is the integer vector of offset values

Excel VBA, 19 characters:

range("WTF").Column

Ruby solution in 26 chars

p ("A"..$*[0]).to_a.size

Real VBA, 216 w/o spaces

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

F# 92 chars :)

---

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

,#.