tags:

views:

1238

answers:

6

I need an algorithm to convert an Excel Column letter to its proper number.

The language this will be written in is C#, but any would do or even pseudo code.

Please note I am going to put this in C# and I don't want to use the office dll.

For 'A' the expected result will be 1

For 'AH' = 34

For 'XFD' = 16384

+2  A: 

Could you perhaps treat it like a base 26 number, and then substitute letters for a base 26 number?

So in effect, your right most digit will always be a raw number between 1 and 26, and the remainder of the "number" (the left part) is the number of 26's collected? So A would represent one lot of 26, B would be 2, etc.

As an example:

B = 2 = Column 2
AB = 26 * 1(A) + 2 = Column 28
BB = 26 * 2(B) + 2 = Column 54
DA = 26 * 4(D) + 1 = Column 105

etc

Chris
+2  A: 

Loop through the characters from last to first. Multiply the value of each letter (A=1, Z=26) times 26**N, add to a running total. My string manipulation skill in C# is nonexistent, so here is some very mixed pseudo-code:

sum=0;
len=length(letters);
for(i=0;i<len;i++)
  sum += ((letters[len-i-1])-'A'+1) * pow(26,i);
Sparr
+13  A: 
public static int ExcelColumnNameToNumber(string columnName)
{
    if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

    char[] characters = columnName.ToUpperInvariant().ToCharArray();

    int sum = 0;

    for (int i = 0; i < characters.Length; i++)
    {
        sum *= 26;
        sum += (characters[i] - 'A' + 1);
    }

    return sum;
}
Ian Nelson
+1 for real code. One fix though, your Pow call should be one order of magnitude smaller. that is... Math.Pow(26.0, characters.Length - i - 1)
Sparr
@Sparr - Thanks, you're right! Edited.
Ian Nelson
Using Math.Pow is actually not the best idea (floating-point issues,performance...) Use 'sum*=26;sum+=(characters[i] -'A'+1);'
Make that "+=(characters[i]-'A')"
@ackb - Good idea, thanks, I've amended my answer to incorporate your suggestion.
Ian Nelson
Awesome works Great, thank you very much.
David Basarab
+7  A: 
int result = colName.Select((c, i) =>
    ((c - 'A' + 1) * ((int)Math.Pow(26, colName.Length - i - 1)))).Sum();
mquander
+1 Beautiful, very elegant! But you fell into the same trap I did with the order of magnitude on the Pow call - should be colName.Length - i - 1.
Ian Nelson
Hmm, thanks, that's correct.
mquander
+3  A: 
int col = colName.ToCharArray().Select(c => c - 'A' + 1).
          Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum();
wethercotes
Perfect. Took me a moment to work why you were reversing (it's because BB is higher than AZ). And you avoid the off by one character length bug others have had by using the index.
Graphain
A: 

You guys need to think outside the square. You do not need such complex coding. The following formula provides the same answer

=MID(ADDRESS(ROW(),COLUMN()),2,FIND("$",ADDRESS(ROW(),COLUMN()),2)-2)

This formula will give you the column letters for any column that you are in. Easily adaptable to change the reference to another column.

Alexis K
You need to learn to understand the questions better. Sorry.
Sergio Acosta
"You guys need to think outside the square.", he was asking for a psuedo code or a C# example, not a Excel inline function example.I think the problem is that you are not thinking outside of Excel!
Anonymous Type