Tutorial :Programming Riddle: How might you translate an Excel column name to a number?



Question:

I was recently asked in a job interview to resolve a programming puzzle that I thought it would be interesting to share. It's about translating Excel column letters to actual numbers, if you recall, Excel names its columns with letters from A to Z, and then the sequence goes AA, AB, AC... AZ, BA, BB, etc.

You have to write a function that accepts a string as a parameter (like "AABCCE") and returns the actual column number.

The solution can be in any language.


Solution:1

I wrote this ages ago 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  


Solution:2

Sounds like a standard reduce to me:

Python:

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

C#:

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


Solution:3

Read a column name from STDIN and print out its corresponding number:

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

Caveats: Assumes ASCII.

EDIT: Replaced " with ' so that your shell won't interpolate $x in the string.


Solution:4

Coincidentally I've 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/


Solution:5

Hah - written it already in our code base - about 3 different 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 isn't actually a Base26 representation of a number (we are lying to ourselves in our function name here) because there is no 0 in it.

The sequence is: A, B, C ... Z, AA, AB, AC

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

(the language is Erlang, mais oui).


Solution:6

You can do this 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;  }  

No error checking, only works for upper case strings, string must be null terminated.


Solution:7

Get the column number from its name

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


Solution:8

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


Solution:9

Get a column name from an int in Java(read more 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;  }  


Solution:10

Caveat: both of these versions assume only uppercase letters A to Z. Anything else causes a miscalculation. It wouldn't be hard to add a bit of error checking and/or uppercasing 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  


Solution:11

Another Delphi one:

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;  


Solution:12

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


Solution:13

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


Solution:14

Does it help to think of the string as the reverse of the column number in base 26 with digits represented by A, B, ... Z?


Solution:15

This is basically a number in base 26, with the difference that the number doesn't use 0-9 and then letters but only letters.


Solution:16

Here's a CFML 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 an odd mood, here's a 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') );  


Solution:17

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


Solution:18

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.


Solution:19

Slightly related, the better challenge is the other way around: given the column number, find the column label as string.

Qt version as what 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;  }  


Solution:20

Common Lisp:

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

edit: the inverse 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))))))  


Solution:21

This version is purely functional and permits alternative 'code' sequences, for example if you wanted to only uses the letters 'A' to 'C'. In Scala, with a suggestion from dcsobral.

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


Solution:22

def ExcelColumnToNumber(ColumnName):      ColNum = 0      for i in range(0, len(ColumnName)):          # Easier once formula determined: 'PositionValue * Base^Position'          # i.e. 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  

p.s. My first Python script!


Solution:23

In Mathematica:

FromDigits[ToCharacterCode@# - 64, 26] &  


Solution:24

Using Mr. Wizard's awesome Mathematica code, but getting rid of the cryptic pure function!

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


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


Solution:26

…just needed a solution for PHP. This 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's 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's number value          $columnNumber = $columnNumber * 26 + $characterNumberValue;      }        //  return the result      return $columnNumber;  }  

Of course the script can be shortened a little by just combining some stuff into one line of code within the foreach loop:

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


Solution:27

In Python, without reduce:

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


Solution: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.  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »