12,626,508 members (39,071 online)
alternative version

10.5K views
6 bookmarked
Posted

# Count With Letters Extension

, 22 Jun 2011 CPOL
 Rate this:
A pair of wrappers about Zeltera's "Count with Letters" code to generate "Integer to Excel Column" and "Excel Column to Integer"

## 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 7th 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;

// Input must be greater than 0 and less than or equal to 16384
// On error: returns an empty string
public string IntToExcelColumn(int value)
{
// Excel is limited to the integer 16384, XFD
if ((value < 1) || (value > MAXINT)) return String.Empty;

// Discussion on converting from a column to 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).

// Discussion on converting from an integer to a column
// The numbers above apply.  If one divides by 676, one gets the value of the third alpha
// Then the remainder by 26 one gets the value of the second alpha; and what is
// left over is the final alpha.

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

//--------------------------------------------------------------
// Computes the excel column integer
// Input is limited to one three alphas with the maximum "XDF"
// On error: returns 0
public int ExcelColumnToInt(string column)
{
// Discussion:
// 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).

string col = column.ToLower();

// Verify a valid input
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:

```//--------------------------------------------------------------
// For internal call to verify valid input
// Expect lower case input
// Feed values 1 through 26 (a-z)
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;
}

//--------------------------------------------------------------
// For internal call to verify valid input
// Expect lower case input
// Feed only one apha letter "a" through "z"
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:

```// From Code Project: "Count with letters" by Zeltera 17 Jun 2007
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

• 21st June, 2011: Initial version

## Share

 Retired United States
Retired C programmer and Unix Sys Admin, then VC6 C++ MFC programmer. I moved to VC7 C++ 2003 in Oct of 04, and VC8 C++/CLI early in 06. I resisted C# for a long time. But now C# is my preferred language. I'm through with upgrading. I'll stay at VC2008 and C# as I only program for fun anymore.

## You may also be interested in...

 First Prev Next
 Off-by-one error? SimmoTech16-Jul-11 0:43 SimmoTech 16-Jul-11 0:43
 Re: Off-by-one error? SimmoTech16-Jul-11 4:14 SimmoTech 16-Jul-11 4:14
 Re: Off-by-one error? Gammill16-Jul-11 18:37 Gammill 16-Jul-11 18:37
 Re: Off-by-one error? JamesHoward97216-Jan-12 22:54 JamesHoward972 16-Jan-12 22:54
 Re: Off-by-one error? Gammill17-Jan-12 13:06 Gammill 17-Jan-12 13:06
 Re: Another succinct method Gammill22-Jun-11 14:19 Gammill 22-Jun-11 14:19
 Re: Another succinct method Jerome Vibert23-Jun-11 1:37 Jerome Vibert 23-Jun-11 1:37
 Re: Another succinct method Gammill23-Jun-11 10:48 Gammill 23-Jun-11 10:48
 A good idea [modified] Jerome Vibert22-Jun-11 4:31 Jerome Vibert 22-Jun-11 4:31
 Re: A good idea Gammill22-Jun-11 9:47 Gammill 22-Jun-11 9:47
 Last Visit: 31-Dec-99 19:00     Last Update: 5-Dec-16 19:49 Refresh 1