CODEWITHSUNDEEP
c#excelmath
David Basarab
David Basarab

Reputation: 73351

What is the algorithm to convert an Excel Column Letter into its Number?

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

Upvotes: 76

Views: 50983

Answers (11)

Tajamul Khanday
Tajamul Khanday

Reputation: 11

Here is a basic c++ answer for those who are intrested in c++ implemention.

int titleToNumber(string given) {
    int power=0;
    int res=0;
    for(int i=given.length()-1;i>=0;i--)
    {
        char c=given[i];
        res+=pow(26,power)*(c-'A'+1);
        power++;    
    }
    return res;     
    }

Upvotes: 1

b-arte-k
b-arte-k

Reputation: 11

For this purpose I use only one line:

int ColumnNumber = Application.Range[MyColumnName + "1"].Column;

Upvotes: 0

Slai
Slai

Reputation: 22886

Shorter version:

int col = "Ab".Aggregate(0, (a, c) => a * 26 + c & 31);  // 28

To ignore non A-Za-z characters:

int col = " !$Af$3 ".Aggregate(0, (a, c) => (uint)((c | 32) - 'a') > 25 ? a : a * 26 + (c & 31)); // 32

Upvotes: 1

B H
B H

Reputation: 1876

I guess this essentially works out pretty much the same as some of the other answers, but it may make a little more clear what's going on with the alpha equivalent of a numeric digit. It's not quite a base 26 system because there is no 0 placeholder. That is, the 26th column would be 'A0' or something instead of Z in base 26. And it's not base 27 because the 'alpha-gits' don't represent powers of 27. Man, it really makes you appreciate what a mess arithmetic must have been before the Babylonians invented the zero!

  UInt32 sum = 0, gitVal = 1;
  foreach (char alphagit in ColumnName.ToUpperInvariant().ToCharArray().Reverse())
  {
    sum += gitVal * (UInt32)(alphagit - 'A' + 1)
    gitVal *= 26;
  }

Like some others, I reversed the character array so I don't need to know anything about exponents.

Upvotes: 0

V. Wolf
V. Wolf

Reputation: 123

in Excel VBA you could use the .Range Method to get the number, like so:

Dim rng as Range
Dim vSearchCol as variant 'your input column
Set rng.Thisworkbook.worksheets("mySheet").Range(vSearchCol & "1:" & vSearchCol & "1")

Then use .column property:

 debug.print rng.column

if you need full code see below:

Function ColumnbyName(vInput As Variant, Optional bByName As Boolean = True) As Variant
    Dim Rng As Range
    If bByName Then
       If Not VBA.IsNumeric(vInput) Then
            Set Rng = ThisWorkbook.Worksheets("mytab").Range(vInput & "1:" & vInput & "1")
            ColumnbyName = Rng.Column
       Else
            MsgBox "Please enter valid non Numeric column or change paramter bByName to False!"
       End If

    Else
        If VBA.IsNumeric(vInput) Then
            ColumnbyName = VBA.Chr(64 + CInt(vInput))
        Else
            MsgBox "Please enter valid Numeric column or change paramter bByName to True!"
        End If

    End If
End Function

Upvotes: 0

Jesse Puente
Jesse Puente

Reputation: 41

Here is a solution I wrote up in JavaScript if anyone is interested.

var letters = "abc".toUpperCase();
var sum = 0;
for(var i = 0; i < letters.length;i++)
{
    sum *= 26;
    sum += (letters.charCodeAt(i) - ("A".charCodeAt(0)-1));
}
alert(sum);

Upvotes: 4

Ian Nelson
Ian Nelson

Reputation: 58763

public static int ExcelColumnNameToNumber(string columnName)
{
    if (string.IsNullOrEmpty(columnName)) throw new ArgumentNullException("columnName");

    columnName = columnName.ToUpperInvariant();

    int sum = 0;

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

    return sum;
}

Upvotes: 128

mqp
mqp

Reputation: 72005

int result = colName.Select((c, i) =>
    ((c - 'A' + 1) * ((int)Math.Pow(26, colName.Length - i - 1)))).Sum();

Upvotes: 18

wethercotes
wethercotes

Reputation: 656

int col = colName.ToCharArray().Select(c => c - 'A' + 1).
          Reverse().Select((v, i) => v * (int)Math.Pow(26, i)).Sum();

Upvotes: 13

Sparr
Sparr

Reputation: 7732

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

Upvotes: 5

Chris
Chris

Reputation: 3517

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

Upvotes: 3

Related Questions