# Useful Code Snippet for finding columns in Excel

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.