Programming Riddle: how can you translate an Excel column name into a number?

Question

I was recently asked for an interview to solve a programming puzzle that I thought would be interesting to share. It's about translating the letters of Excel columns into actual numbers, if you remember, Excel calls its columns letters from A to Z, and then the sequence goes to AA, AB, AC ... AZ, BA, BB, etc.

You need to write a function that takes a string as a parameter (for example, "AABCCE") and returns the actual column number.

The solution can be in any language.

Answer 1

I wrote these eyelids back for some Python script:

def index_to_int(index): s = 0 pow = 1 for letter in index[::-1]: d = int(letter,36) - 9 s += pow * d pow *= 26 # excel starts column numeration from 1 return s 

Answer 2

Sounds like a standard abbreviation to me:

Python:

 def excel2num(x): return reduce(lambda s,a:s*26+ord(a)-ord('A')+1, x, 0) 

FROM#:

 int ExcelToNumber(string x) { return x.Aggregate(0, (s, c) => s * 26 + c - 'A' + 1 ); } 

Answer 3

Read the column name from STDIN and print its corresponding number:

 perl -le '$x = $x * 26 - 64 + ord for <> =~ /./g; print $x' 

Cautions: ASCII assumed.

EDIT: Replaces " with ' so that your shell does not interpolate $x in the string.

Answer 4

By the way, I solved the same problem using javascript

 $(function() { //shorthand document.ready function var getNumber = function(x) { var result = 0; var multiplier = 1; for ( var i = x.length-1; i >= 0; i--) { var value = ((x[i].charCodeAt(0) - "A".charCodeAt(0)) + 1); result = result + value * multiplier; multiplier = multiplier * 26; } return result; }; $('#form').on('submit', function(e) { //use on if jQuery 1.7+ e.preventDefault(); //prevent form from submitting var data = $("#number").val(); $('#answer').text(getNumber(data)); }); }); 

 <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script> <form id="form"> <input type="text" id="number"></input> <button>submit</button> </form> <p id="answer"></p> 

  var getNumber = function(x) { var result = 0; var multiplier = 1; for ( var i = x.length-1; i >= 0; i--) { var value = ((x[i].charCodeAt(0) - "A".charCodeAt(0)) + 1); result = result + value * multiplier; multiplier = multiplier * 26; } return result; }; 

http://jsfiddle.net/M7Xty/1/

Answer 5

Hah - wrote it down already in our code base - about 3 times :(

 %% @doc Convert an string to a decimal integer %% @spec b26_to_i(string()) -> integer() b26_to_i(List) when is_list(List) -> b26_to_i(string:to_lower(lists:reverse(List)),0,0). %% private functions b26_to_i([], _Power, Value) -> Value; b26_to_i([H|T],Power,Value)-> NewValue = case (H > 96) andalso (H < 123) of true -> round((H - 96) * math:pow(26, Power)); _ -> exit([H | T] ++ " is not a valid base 26 number") end, b26_to_i(T, Power + 1, NewValue + Value). 

The riddle is that it is not a basic representation of a number (here we call ourselves here as a function), because it does not have 0.

Sequence: A, B, C ... Z, AA, AB, AC

not: A, B, C ... Z, BA, BB, BC

(language - Erlang, mais oui).

Answer 6

You can do it in C, like this:

 unsigned int coltonum(char * string) { unsigned result = 0; char ch; while(ch = *string++) result = result * 26 + ch - 'A' + 1; return result; } 

There is no error checking, it works only for uppercase strings, the string should be terminated with zero.

Answer 7

Assuming column A = 1

 int GetColumnNumber(string columnName) { int sum = 0; int exponent = 0; for(int i = columnName.Length - 1; i>=0; i--) { sum += (columnName[i] - 'A' + 1) * (GetPower(26, exponent)); exponent++; } return sum; } int GetPower(int number, int exponent) { int power = 1; for(int i=0; i<exponent; i++) power *= number; return power; } 

Answer 8

Caution: both versions allow only uppercase letters from A to Z. Everything else causes a miscalculation. It’s easy to add some error checking and / or upper case to improve them.

Scala

 def excel2Number(excel : String) : Int = (0 /: excel) ((accum, ch) => accum * 26 + ch - 'A' + 1) 

Haskell

 excel2Number :: String -> Int excel2Number = flip foldl 0 $ \accum ch -> accum * 26 + fromEnum ch - fromEnum 'A' + 1 

Answer 9

Get the column number on his behalf

Java:

 public int getColNum (String colName) { //remove any whitespace colName = colName.trim(); StringBuffer buff = new StringBuffer(colName); //string to lower case, reverse then place in char array char chars[] = buff.reverse().toString().toLowerCase().toCharArray(); int retVal=0, multiplier=0; for(int i = 0; i < chars.length;i++){ //retrieve ascii value of character, subtract 96 so number corresponds to place in alphabet. ascii 'a' = 97 multiplier = (int)chars[i]-96; //mult the number by 26^(position in array) retVal += multiplier * Math.pow(26, i); } return retVal; } 

Answer 10

Get the column name from int in Java ( more info here ):

 public String getColName (int colNum) { String res = ""; int quot = colNum; int rem; /*1. Subtract one from number. *2. Save the mod 26 value. *3. Divide the number by 26, save result. *4. Convert the remainder to a letter. *5. Repeat until the number is zero. *6. Return that bitch... */ while(quot > 0) { quot = quot - 1; rem = quot % 26; quot = quot / 26; //cast to a char and add to the beginning of the string //add 97 to convert to the correct ascii number res = (char)(rem+97) + res; } return res; } 

Answer 11

Another Delphi:

 function ExcelColumnNumberToLetter(col: Integer): string; begin if (col <= 26) then begin Result := Chr(col + 64); end else begin col := col-1; Result := ExcelColumnNumberToLetter(col div 26) + ExcelColumnNumberToLetter((col mod 26) + 1); end; end; 

Answer 12

Other Java:

 public static int convertNameToIndex(String columnName) { int index = 0; char[] name = columnName.toUpperCase().toCharArray(); for(int i = 0; i < name.length; i++) { index *= 26; index += name[i] - 'A' + 1; } return index; } 

Answer 13

Simple Java Solution →

 public class ColumnName { public static int colIndex(String col) { int index=0; int mul=0; for(int i=col.length()-1;i>=0;i--) { index += (col.charAt(i)-64) * Math.pow(26, mul); mul++; } return index; } public static void main(String[] args) { System.out.println(colIndex("AAA")); } 

Answer 14

Does this help to think of the row as the inverse of the column number in base 26 with the numbers represented by A, B, ... Z?

Answer 15

This is basically a number in the base of 26, with the difference that the number does not use 0-9, and then the letters, but only .

Answer 16

Here CFML is one:

 <cffunction name="ColToNum" returntype="Numeric"> <cfargument name="Input" type="String" /> <cfset var Total = 0 /> <cfset var Pos = 0 /> <cfloop index="Pos" from="1" to="#Len(Arguments.Input)#"> <cfset Total += 26^(Pos-1) * ( Asc( UCase( Mid(Arguments.Input,Pos,1) ) ) - 64 ) /> </cfloop> <cfreturn Total /> </cffunction> <cfoutput> #ColToNum('AABCCE')# </cfoutput> 

And because I'm in a weird mood, here is the CFScript version:

 function ColToNum ( Input ) { var Total = 0; for ( var Pos = 1 ; Pos <= Len(Arguments.Input) ; Pos++ ) { Total += 26^(Pos-1) * ( Asc( UCase( Mid(Arguments.Input,Pos,1) ) ) - 64 ); } return Total; } WriteOutput( ColToNum('AABCCE') ); 

Answer 17

Common Lisp:

 (defun excel->number (string) "Converts an Excel column name to a column number." (reduce (lambda (ab) (+ (* a 26) b)) string :key (lambda (x) (- (char-int x) 64)))) 

edit: reverse operation:

 (defun number->excel (number &optional acc) "Converts a column number to Excel column name." (if (zerop number) (concatenate 'string acc) (multiple-value-bind (rest current) (floor number 26) (if (zerop current) (number->excel (- rest 1) (cons #\Z acc)) (number->excel rest (cons (code-char (+ current 64)) acc)))))) 

Answer 18

A bit connected, the best challenge is the other way around: given the column number, find the column label as a string.

Qt version, as I implemented for KOffice:

 QString columnLabel( unsigned column ) { QString str; unsigned digits = 1; unsigned offset = 0; column--; for( unsigned limit = 26; column >= limit+offset; limit *= 26, digits++ ) offset += limit; for( unsigned c = column - offset; digits; --digits, c/=26 ) str.prepend( QChar( 'A' + (c%26) ) ); return str; } 

Answer 19

another [more cryptic] erlang example:

 col2int(String) -> col2int(0,String). col2int(X,[A|L]) when A >= 65, A =< 90 -> col2int(26 * X + A - 65 + 1, L); col2int(X,[]) -> X. 

and inverse function:

 int2col(Y) when Y > 0 -> int2col(Y,[]). int2col(0,L) -> L; int2col(Y,L) when Y rem 26 == 0 -> int2col(Y div 26 - 1,[(26+65-1)|L]); int2col(Y,L) -> P = Y rem 26, int2col((Y - P) div 26,[P + 65-1|L]). 

Answer 20

Delphi:

 // convert EXcel column name to column number 1..256 // case-sensitive; returns 0 for illegal column name function cmColmAlfaToNumb( const qSRC : string ) : integer; var II : integer; begin result := 0; for II := 1 to length(qSRC) do begin if (qSRC[II]<'A')or(qSRC[II]>'Z') then begin result := 0; exit; end; result := result*26+ord(qSRC[II])-ord('A')+1; end; if result>256 then result := 0; end; 

Al.

Answer 21

 def ExcelColumnToNumber(ColumnName): ColNum = 0 for i in range(0, len(ColumnName)): # Easier once formula determined: 'PositionValue * Base^Position' # ie AA=(1*26^1)+(1*26^0) or 792=(7*10^2)+(9*10^1)+(2*10^0) ColNum += (int(ColumnName[i],36) -9) * (pow(26, len(ColumnName)-i-1)) return ColNum 

ps My first Python script!

Answer 22

This version is purely functional and allows alternative "code" sequences, for example, if you want to use the letters "A" and "C". In Scala, with an offer from dcsobral.

 def columnNumber(name: String) = { val code = 'A' to 'Z' name.foldLeft(0) { (sum, letter) => (sum * code.length) + (code.indexOf(letter) + 1) } } 

Answer 23

In Mathematica:

 FromDigits[ToCharacterCode@# - 64, 26] & 

Answer 24

Using Mr. Master's amazing Mathematica code, but getting rid of a cryptic clean function!

 columnNumber[name_String] := FromDigits[ToCharacterCode[name] - 64, 26] 

Answer 25

Wikipedia has good explanations and algos

http://en.wikipedia.org/wiki/Hexavigesimal

 public static String toBase26(int value){ // Note: This is a slightly modified version of the Alphabet-only conversion algorithm value = Math.abs(value); String converted = ""; boolean iteration = false; // Repeatedly divide the number by 26 and convert the // remainder into the appropriate letter. do { int remainder = value % 26; // Compensate for the last letter of the series being corrected on 2 or more iterations. if (iteration && value < 25) { remainder--; } converted = (char)(remainder + 'A') + converted; value = (value - remainder) / 26; iteration = true; } while (value > 0); return converted; } 

Answer 26

... just need a solution for PHP . Here is what I came up with:

 /** * Calculates the column number for a given column name. * * @param string $columnName the column name: "A", "B", …, "Y", "Z", "AA", "AB" … "AZ", "BA", … "ZZ", "AAA", … * * @return int the column number for the given column name: 1 for "A", 2 for "B", …, 25 for "Y", 26 for "Z", 27 for "AA", … 52 for "AZ", 53 for "BA", … 703 for "AAA", … */ function getColumnNumber($columnName){ // the function result $columnNumber = 0; // at first we need to lower-case the string because we calculate with the ASCII value of (lower-case) "a" $columnName = strtolower($columnName); // ASCII value of letter "a" $aAsciiValue = ord('a') - 1; // iterate all characters by splitting the column name foreach (str_split($columnName) as $character) { // determine ASCII value of current character and substract with that one from letter "a" $characterNumberValue = ord($character) - $aAsciiValue; // through iteration and multiplying we finally get the previous letters' values on base 26 // then we just add the current character number value $columnNumber = $columnNumber * 26 + $characterNumberValue; } // return the result return $columnNumber; } 

Of course, the script can be shortened a bit by simply combining some things into one line of code inside the foreach loop:

 // … $columnNumber = $columnNumber * 26 + ord($character) - ord('a') + 1; // … 

Answer 27

In Python, without abbreviation:

 def transform(column_string): return sum((ascii_uppercase.index(letter)+1) * 26**position for position, letter in enumerate(column_string[::-1])) 

Answer 28

Here is another version of this code in Python:

 keycode=1 for i in range (1,len(word)): numtest[i]=word[i-1] keycode = keycode*26*int(wordtest[numtest[i]]) last=word[-1:] keycode=keycode+int(wordtest[last]) print(keycode) print(bin(keycode)) #Numtest and wordtest are dictionaries.