Count With Letters Extension

• Download source - 1.52 KB
• Download ExcelUtils library source - 4.15 KB

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;

// 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

• 21^st June, 2011: Initial version