Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

People also ask

How do I get column names from column numbers in Excel?

In a row of Excel, e.g. cell A1, enter the column number =column() In the row below, enter =Address(1,A1) This will provide the result $A$1.

How do you name a column in Excel?

Select a column, and then select Transform > Rename. You can also double-click the column header. Enter the new name.


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

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

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.


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/


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


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.