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.

Answer 1

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?

Answer 2

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.

Answer 3

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

Answer 4

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 5

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

Answer 6

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.

Answer 7

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

Answer 8

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

Answer 9

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 10

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 11

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!

Answer 12

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

Answer 13

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

Answer 14

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

Answer 15

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.

Answer 16

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

Answer 17

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