## Introduction

Should you need to count as Excel counts columns, then look at Zeltera's "Count with Letters".

His routines can be extended to convert an Integer to an Excel Column or an Excel Column to an integer. A long time ago, I wrote two very simple, inefficient loop wrappers around Zeltera's core code to do just that. Here I present a way to calculate a result, so as to reduce the loop to no more than 26 passes.

## Background

Zeltera's routines nicely count from "`a`

" through "`z`

", then step onto "`aa`

" through "`xdf`

" (Excel's maximum). As I wanted to "just get the job done", I wrote the following little wrappers. (Here minimized). These simply loops form "`a`

" to whatever. The computer is fast and I didn't have to process a large number of columns, so the compute time wasn't significant. (I'm sure you too have written code you knew to be inefficient.)

string IntToExcelColumn(int value)
string str = "a";
for (int inx = 1; inx < value; inx++) { str = GetNext(str); }
return str;
int ExcelColumnToInt(string column)
for (int inx = 1; inx < 16384; inx++)
{
string str = IntToExcelColumn(inx).ToLower();
if (String.Compare(col, str) == 0) { return inx; }
}

## Discussion of the Code

Excel limits the number of columns to XDF, which is 16384 columns. It's obvious that with only one alpha, one goes from 1 to 26, columns A through Z. So, it follows that to get to AA one would go through 26 twice. And thus, as G is the 7^{th} letter in the alphabet, AG is computed as (26 * 2) + 7 = 59. Following that line of reasoning, AAA must be numeric 676 (26 x 26 = 676) And then all one has to do is generalize.

I came up with these rules (Examples for: "G", "GG", "GGG") to obtain an integer:

1) For one alpha: The numeric value within the alphabet.
Thus: (G = 7)
2) For two alphas: the numeric value of the most significant times 26
plus the numeric value of the lest significant.
Thus: (G# = 26 x 7 = 182) + (#G = 7) = 189
3) For three alphas: the numeric value of the most significant time 676
plus 26 times the next most significant plus the numeric value of the lest significant.
Thus (G## = 676 x 7 = 4732) + (#G# = 182) + (##G = 7) = 4921
4) No further as Excel is limited to three alphas (XFD, 16384).

These may be used to generate the alpha columns as well. If one divides by 676, one gets the value of the most significant alpha, then the remainder by 26 one gets the value of the next most significant alpha; and what is left over is the lest significant alpha.

## The Code

const int MAXINT = 16384;
const int TWOALPHAS = 26;
const int THREEALPHAS = 676;
public string IntToExcelColumn(int value)
{
if ((value < 1) || (value > MAXINT)) return String.Empty;
int remainder = value;
int[] aryInt = new int[3];
string[] aryStr = new string[3];
if (remainder > THREEALPHAS)
{
aryInt[2] = remainder / THREEALPHAS;
remainder = remainder % THREEALPHAS;
aryStr[2] = CountUpToValue(aryInt[2]);
aryInt[1] = remainder / TWOALPHAS;
remainder = remainder % TWOALPHAS;
aryStr[1] = CountUpToValue(aryInt[1]);
}
else if (value > TWOALPHAS)
{
aryInt[1] = remainder / TWOALPHAS;
remainder = remainder % TWOALPHAS;
aryStr[1] = CountUpToValue(aryInt[1]);
}
aryInt[0] = remainder;
aryStr[0] = CountUpToValue(aryInt[0]);
string rtn = aryStr[2] + aryStr[1] + aryStr[0];
return rtn.ToUpper();
}
public int ExcelColumnToInt(string column)
{
string col = column.ToLower();
if (col.Length > 3) return 0;
int[] aryInt = new int[3];
int[] aryCalc = new int[3];
for (int inx = 0; inx < col.Length; inx++)
{
aryInt[inx] = CountToAlpha(col.Substring(inx, 1));
if (aryInt[inx] == 0) return 0;
if (col.Length == 1)
{
aryCalc[inx] = aryInt[inx];
if (aryInt[inx] == 0) return 0;
}
else if (col.Length == 2)
{
switch (inx)
{
case 0: aryCalc[inx] = aryInt[inx] * TWOALPHAS; break;
case 1: aryCalc[inx] = aryInt[inx]; break;
}
}
else
{
switch (inx)
{
case 0: aryCalc[inx] = aryInt[inx] * THREEALPHAS; break;
case 1: aryCalc[inx] = aryInt[inx] * TWOALPHAS; break;
case 2: aryCalc[inx] = aryInt[inx]; break;
}
}
}
int rtn = aryCalc[0] + aryCalc[1] + aryCalc[2];
if (rtn > MAXINT) return 0;
return rtn;
}

And you need these two little wrappers:

private string CountUpToValue(int value)
{
string str = "a";
if (value == 1) return str;
for (int inx = 1; inx < value; inx++)
{
str = GetNext(str);
}
return str;
}
private int CountToAlpha(string column)
{
string str;
for (int inx = 1; inx < (MAXINT + 1); inx++)
{
str = CountUpToValue(inx);
if (String.Compare(column, str) == 0)
{
return inx;
}
}
return 0;
}

And finally, you need this Zeltera code:

private String GetNext(String currentString)
{
currentString = currentString.ToLower();
if (currentString.Length == 0)
return "a";
Char lastCharacter = currentString[currentString.Length - 1];
String subString = currentString.Substring(0, currentString.Length - 1);
if (lastCharacter == 'z')
return GetNext(subString) + 'a';
else
return subString + (char)((int)lastCharacter + 1);
}

## Points of Interest

With the processing speeds so greatly improved from the inefficient loops to a few calculations, it was a short step from...

public string IntToExcelColumn(int value)

...and...

public int ExcelColumnToInt(string column)

...to write additional range like wrappers which use them:

public List<string> IntToExcelColumnsList(string start, int count)
public List<string> IntToExcelColumnsList(int first, int count)
public List<string> ColumnToExcelColumnList(string start, int count)
public List<string> ColumnToExcelColumnList(int start, int end)
public List<string> ColumnToExcelColumnList(string start, string end)

If you want them, download the source to `ExcelUtils `

library. There's some other stuff in there too. No promises on the other code. Test it before you use it as I haven't used it in a long time.

## History

- 21
^{st} June, 2011: Initial version