What is the algorithm for converting an excel column letter to its number?

Question

I need an algorithm to convert an Excel column letter to its correct number.

The language in which it will be written is C #, but anyone will do it, or even pseudo-code.

Please note that I am going to put this in C # and I do not want to use dll office.

For "A", the expected result will be 1

For 'AH' = 34

For "XFD" = 16384

Answer 1

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

Answer 2

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

Answer 3

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

Answer 4

Scroll characters from last to first. Multiply the value of each letter (A = 1, Z = 26) times 26 ** N, add to the total. My skill in string manipulation in C # does not exist, so here are some very mixed pseudo codes:

 sum=0; len=length(letters); for(i=0;i<len;i++) sum += ((letters[len-i-1])-'A'+1) * pow(26,i); 

Answer 5

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

Thus, your rightmost digit will always be a raw number from 1 to 26, and the remaining part of the "number" (left side) is the number of 26 collected? So A will represent one batch of 26, B will 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.

Answer 6

Here is the solution I wrote 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); 

Answer 7

I am not very happy with any of the answers, so here is a short version:

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

or better, to ignore A-Za-z characters:

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

Answer 8

in Excel VBA, you can use the .Range method to get the number, for example:

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

Then use the .column property:

  debug.print rng.column 

if you need the 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 

Answer 9

I suppose this essentially works the same way as some of the other answers, but it may be a little more clear what happens to the alpha equivalent of a numeric digit. This is not quite a basic system, because there is no numbering. That is, the 26th column will be “A0” or something instead of Z in base 26. And this is not based on 27 because the “alpha gits” are not possible 27. Man, it really makes you appreciate what kind of mess arithmetic had to be before the Babylonians invented zero!

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

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

Answer 10

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

= MID (ADDRESS (STRING (), Column ()), 2, FIND ("$", address (STRING (), Column ()), 2) -2)

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