Click here to Skip to main content
12,626,508 members (39,071 online)
Click here to Skip to main content
Add your own
alternative version

Stats

10.5K views
245 downloads
6 bookmarked
Posted

Count With Letters Extension

, 22 Jun 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Gammill
Retired
United States 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...

Comments and Discussions

 
QuestionOff-by-one error? Pin
SimmoTech16-Jul-11 0:43
memberSimmoTech16-Jul-11 0:43 
AnswerRe: Off-by-one error? Pin
SimmoTech16-Jul-11 4:14
memberSimmoTech16-Jul-11 4:14 
AnswerRe: Off-by-one error? Pin
Gammill16-Jul-11 18:37
memberGammill16-Jul-11 18:37 
AnswerRe: Off-by-one error? Pin
JamesHoward97216-Jan-12 22:54
memberJamesHoward97216-Jan-12 22:54 
GeneralRe: Off-by-one error? Pin
Gammill17-Jan-12 13:06
memberGammill17-Jan-12 13:06 
QuestionAnother succinct method Pin
Ian Shlasko22-Jun-11 11:53
memberIan Shlasko22-Jun-11 11:53 
AnswerRe: Another succinct method Pin
Gammill22-Jun-11 14:19
memberGammill22-Jun-11 14:19 
AnswerRe: Another succinct method Pin
Jerome Vibert23-Jun-11 1:37
memberJerome Vibert23-Jun-11 1:37 
GeneralRe: Another succinct method Pin
Gammill23-Jun-11 10:48
memberGammill23-Jun-11 10:48 
QuestionA good idea [modified] Pin
Jerome Vibert22-Jun-11 4:31
memberJerome Vibert22-Jun-11 4:31 
AnswerRe: A good idea Pin
Gammill22-Jun-11 9:47
memberGammill22-Jun-11 9:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.161205.3 | Last Updated 22 Jun 2011
Article Copyright 2011 by Gammill
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid