Incrementing alphabets

Question

I am trying to create a function which will give me alphabet position when an index is passed. It will be same like how excel shows it's columns. A...Z, AA,AB.... I wrote the below function to get the results upto Z. It looks like

static string GetColumnName(int index)
{
    const int alphabetsCount = 26;
    if (index <= alphabetsCount)
    {
        int code = (index - 1) + (int)'A';
        return char.ConvertFromUtf32(code);
    }
    return string.Empty;
}

This works fine until 'Z'. It return 'A' if I pass 1 and return 'B' if I pass 2 and so on. But, I am not able to figure out how will I get AA when I pass 27 to this function. I guess I need a recursive method to find it.

Any inputs to this problem will be great!

Edit

This is suggested by Tordek. But his code will fail in numbers like 52, 78 etc. Added workaround for that and here is the final working code.

static string GetColumnName(int index)
{
    const int alphabetsCount = 26;

    if (index > alphabetsCount)
    {
        int mod = index % alphabetsCount;
        int columnIndex = index / alphabetsCount;

        // if mod is 0 (clearly divisible) we reached end of one combination. Something like AZ
        if (mod == 0)
        {
            // reducing column index as index / alphabetsCount will give the next value and we will miss one column.
            columnIndex -= 1;
            // passing 0 to the function will return character '@' which is invalid
            // mod should be the alphabets count. So it takes the last char in the alphabet.
            mod = alphabetsCount;
        }
        return GetColumnName(columnIndex) + GetColumnName(mod);
    }
    else
    {
        int code = (index - 1) + (int)'A';
        return char.ConvertFromUtf32(code);
    }
}

score 4 · Answer 1 · edited May 23 '17 at 12:30

4

See this question:
Translate a column index into an Excel Column Name

or this one:
How to convert a column number (eg. 127) into an excel column (eg. AA)

Though the first link has a correct answer right at the top and the 2nd has several that are not correct.

edited May 23 '17 at 12:30

Community

1
1

answered May 29 '09 at 02:26

Joel Coehoorn

399,467
113
570
794

score 4 · Accepted Answer · answered May 29 '09 at 02:33

Any recursive function can be converted into an equivalent iterative one. I find it always easy to think recursively first:

static string GetColumnName(int index)
{
    const int alphabetsCount = 26;

    if (index > alphabetsCount) {
        return GetColumnName(index / alphabetsCount) + GetColumnName(index % alphabetsCount);
    } else {
        int code = (index - 1) + (int)'A';
        return char.ConvertFromUtf32(code);
    }
}

Which can be simple converted into:

static string GetColumnName(int index)
{
    const int alphabetsCount = 26;
    string result = string.Empty;

    while (index > 0) {
        result = char.ConvertFromUtf32(64 + (index % alphabetsCount)) + result;
        index /= alphabetsCount;
    }

    return result;
}

Even so, listen to Joel.

Great. My math is rusty. So couldn't figure out modulo operation. Thanks for the code. — Navaneeth K N, May 29 '09 at 03:36

score 0 · Answer 3 · answered May 29 '09 at 02:20

0

Recursion is one possibility -- if index > 26, you deal with index % 26 in this call and concatenate it to a recursive call on index / 26. However, iteration is often speedier and not hard to arrange for simple cases such as this one. In pseudocode:

string result = <convert `index % 26`>
while index > 26:
  index = index / 26
  result = <convert `index % 26`> + result
return result

or the like.

answered May 29 '09 at 02:20

Alex Martelli

854,459
170
1,222
1,395

There's nothing wrong with your pseudocode, but it's important to know that you should probably use the StringBuilder class if you're going to be appending to a string in a loop, to save on object allocations: http://msdn.microsoft.com/en-us/library/system.text.stringbuilder(loband).aspx – Paul Fisher May 29 '09 at 02:30
This is wrong because it is not a base 26 system. If A would be 0, A would equal AA (0 == 00). If A would be 1, going from Z to AA would be like going from 9 to 11. – Daniel Brückner May 29 '09 at 02:31
Paul, StringBuilder is overkill. A 32-bit int is 2^32 and 26^7 overflows it so the max is 7 letters long. 7 iterations is hardly taxing. A 64-bit long would only have 14 letters. :) – Colin Burnett May 29 '09 at 02:43

score 0 · Answer 4 · answered May 29 '09 at 02:24

static string GetColumnName(int index)
{
    const int alphabetsCount = 26;
    string result = '';

    if (index >= alphabetsCount)
    {
        result += GetColumnName(index-alphabetsCount)
    }
    return (string) (64 + index);
}

My C# is HORRIBLE AND RUSTY. Interpret this as pseudocode - it will almost certainly not compile, but may get you started.

Incrementing alphabets

4 Answers4

Linked

Related