26

Does any one have algorithm or logic to Convert A to 1 ,B to 2, ... ,Z to 26 and then ,AA to 27, AB to 28 etc.

In other words, converting a column index into the column reference in Excel.

casperOne
  • 73,706
  • 19
  • 184
  • 253
Thunder
  • 10,366
  • 25
  • 84
  • 114

5 Answers5

38

Here's a simple LINQ expression:

static int TextToNumber(this string text) {
    return text
        .Select(c => c - 'A' + 1)
        .Aggregate((sum, next) => sum*26 + next);
}

This test 

Console.WriteLine(" A -> " + "A".TextToNumber());
Console.WriteLine(" B -> " + "B".TextToNumber());
Console.WriteLine(" Z -> " + "Z".TextToNumber());
Console.WriteLine("AA -> " + "AA".TextToNumber());
Console.WriteLine("AB -> " + "AB".TextToNumber());

will produce this output:

 A -> 1
 B -> 2
 Z -> 26
AA -> 27
AB -> 28

Update: Here's the same code but targetting .NET 2.0:

static int TextToNumber(string text) {
    int sum = 0;
    foreach (char c in text) {
        sum = sum*26 + c - 'A' + 1;
    }
    return sum;
}
Thomas Freudenberg
  • 5,048
  • 1
  • 35
  • 44
  • 2
    +1 for using a fold. You don't even need to convert the string to a char array, since a string behaves like an IEnumerable already. E.g. `s.Select(c => c - 'A' + 1).Aggregate((sum, next) => sum * 26 + next)` – cfern Dec 23 '09 at 09:39
  • Thx cfern. I forgot that string implements IEnumerable. I've updated my answer – Thomas Freudenberg Dec 23 '09 at 09:47
  • 1
    Neat solution but Linq is not supported in visual studio 2005 – Thunder Dec 23 '09 at 11:42
  • Brah... you need to ToCharArray() that string in 3.5 b4 you throw down a Select – Merritt Apr 12 '12 at 21:51
  • @Merritt I guess it should be sufficient to include the `System.Linq` namespace – Thomas Freudenberg Apr 14 '12 at 10:15
  • My bad: it's actually hidden by intellisense for some reason... http://stackoverflow.com/questions/345883/why-doesnt-vs-2008-display-extension-methods-in-intellisense-for-string-class – Merritt Apr 16 '12 at 18:46
  • Nice Answer, how about NumberToText Method in both aswell? – Pierre Jun 19 '13 at 11:24
33

Have a look at these

/// <summary>
/// 1 -> A<br/>
/// 2 -> B<br/>
/// 3 -> C<br/>
/// ...
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static string ExcelColumnFromNumber(int column)
{
    string columnString = "";
    decimal columnNumber = column;
    while (columnNumber > 0)
    {
        decimal currentLetterNumber = (columnNumber - 1) % 26;
        char currentLetter = (char)(currentLetterNumber + 65);
        columnString = currentLetter + columnString;
        columnNumber = (columnNumber - (currentLetterNumber + 1)) / 26;
    }
    return columnString;
}

/// <summary>
/// A -> 1<br/>
/// B -> 2<br/>
/// C -> 3<br/>
/// ...
/// </summary>
/// <param name="column"></param>
/// <returns></returns>
public static int NumberFromExcelColumn(string column)
{
    int retVal = 0;
    string col = column.ToUpper();
    for (int iChar = col.Length - 1; iChar >= 0; iChar--)
    {
        char colPiece = col[iChar];
        int colNum = colPiece - 64;
        retVal = retVal + colNum * (int)Math.Pow(26, col.Length - (iChar + 1));
    }
    return retVal;
}
Adriaan Stander
  • 162,879
  • 31
  • 289
  • 284
3

This is a code for JavaScript if you prefer it done on the client side

<script type="text/javascript" lang="javascript">
function Alphabet2Numeric(mystr) {
    mystr = mystr.toUpperCase(); //Hence the ASCII code 64 down there
    var sum = 0;
    for (var i = 0; i < mystr.length; i++) {
        sum = sum * 26 + mystr.charCodeAt(i) - 64; //returns 1 for 'a' and 2 for 'b' so on and so forth.
    }
    return sum;
}
</script>
Gama Sharma
  • 51
  • 1
  • 7
0

Same problem, different language: PHP.

function charToInt($char)
{
   $array = array_flip(range(a, z));
   return $array[$char] + 1;
}

echo charToInt('c');

outputs: 3
Jens A. Koch
  • 39,862
  • 13
  • 113
  • 141
0

From A to ZZ : ((Number of characters - 1) * 26 ) + ASCII of last character%65 + ASCII of 1st character%65 * (26^(length of characters -1)) -1 [Add only if positive] enter image description here

Rkmr039
  • 175
  • 2
  • 6