Hi all,

I have been working on a project for a while now and needed a quick converter between an integer index and the column of an Excel spreadsheet, I couldn’t find a general algorithm, so had to write one…

At first I thought it was a simple base 26 (26 letters in the alphabet) system, but then realized that with the first column being indexed as 0 or 1 there would be an inconsistency when you get to the 26th column (where it should read “AA”, or “10” in a base 26 system), “A” is both representing the values 0 and 1.

I worked for a while on an algorithm to calculate the correct value and have come up with the following static class to do it:

**Converting between integer index and Column string:**

`using System;`

`using System.Diagnostics;`

`/// <summary>`

`/// Converter from numberical index of column to the identifier in excel`

`/// </summary>`

public static class Column{`/// <summary>`

`/// Array of alphabet`

`/// </summary>`

private static string[] alphabet = { "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z" };`/// <summary>`

`/// Converts from numberical index of column to the identifier in excel`

`/// </summary>`

`/// <param name="index">The index of the column</param>`

`/// <returns>The corrisponding letter string of that column</returns>`

public static string Index(int index){string value = string.Empty;`int Base = alphabet.Length;`

`int currentPower = 0;`

`if (index >= 0)`

{`do`

{if (Math.Pow(Base, currentPower) < int.MaxValue){value = alphabet[((index - RecursivePower(Base, currentPower)) / (int)Math.Pow(Base, currentPower)) % Base] + value;currentPower++;}`else`

{`Trace.WriteLine("That index is too high for the value of MaxInt on this system, so can not continue");`

return string.Empty;}}`while (RecursivePower(Base, currentPower) <= index);`

}return value;}`/// <summary>`

`/// Do a recursive summing of powers from starting power to 1`

`/// </summary>`

`/// <param name="theBase"></param>`

`/// <param name="startingPower"></param>`

`/// <returns>The sum of the powers of theBase to 1 (0 if power starts > 1)</returns>`

private static int RecursivePower(int theBase, int startingPower){int value = 0;for (int i = startingPower; i > 0; i--){value += (int) Math.Pow(theBase, i);}return value;}}I hope that helps anyone trying to do this… again it can be ported into VB if you want to script it, and remember where I used alphabet as an array you could always use Chr(

index calculation here+ 65) which I believe indexes the “A” character when 0 is added to it.

Are you trolling for interview candidates? 😉

string ColumnName(unsigned n) {

vector<char> digits;

while (n > 25) {

digits.push_back(n % 26 + ‘A’);

n = n / 26 – 1;

}

digits.push_back(n + ‘A’);

return string(digits.rbegin(), digits.rend());

}

C++ guy:

no, to be honest I tried looking for an algorithm in c# for a while without finding one, so tried figuring it out myself.

I have just ported your code into C# and have:

public string ColumnName(uint n)

{

string digits = string.Empty;

while (n > 25)

{

digits = ((char)(n % 26 + (int)’A’)) + digits;

n = n / 26 – 1;

}

digits = ((char)(n + (int)’A’)) + digits;

return digits;

}

which works nicely, though I’m not too happy about casting from chars to ints in a higher level language, but thanks still. I’m just surprised that I couldn’t find this code when I was looking before :