Thursday, March 5, 2015

Excel column name from number

In the run-up to a comprehensive post about using the Excel COM object in X++ (because there's just no substitute for the real deal), here's a little problem I ran into along the way: Getting the Excel column name right.

There are a lot of algorithms out there, but I like none of them. This one is OLD SCHOOL!!

str getColStr(int _num)
{   int b = 26;
    int d = (a div b) div b;
    int f = _num mod b;
    int g = (a div b) mod b;
    str res;

    if (_num > 0 && _num < 16385)
        res = strReplace(strFmt("%1%2%3", num2char(d+64), num2char(g+64), num2char(f+64)), "@", "")
        return res;

In short: because of the 16385 column limitation of Excel, there is really no need to make it more complicated than it is. Three letters is the max, or column 'XFD' if you must know.
The first (rightmost) column letter is always the modulo of the number and 26 (letters in alphabet, so that one is easy.
The second (if any) column letter is the modulo of the remainder and 26.
The third column finally is the number of times the remainder can be divided by 26.

Example: what is column 1189 in Excel?

first column letter: 1189 MOD 26 = 19 > "S"
Remainder (how many times does 26 go into 1189 or 1189 DIV 26) : 45
Second column letter: remainder MOD 26 = 19 > "S"
Remainder (how many times does 26 go into 45 or 45 DIV 26) : 1 > "A"
Third column letter is the same as the remainder because we know there cannot be a greater power on this earth or the next. Besides, if you try to create a spreadsheet with over 16,000 columns you really should seek professional help in a different area of expertise.
So 1189 returns "ASS" and lo and behold...that is correct.
Why so serious, batman?

Speaking of which, why is it that all the examples I came across on the various blogs are so much more complex? It took me more time to google bad examples than to write this little ditty.
Is it just because only dinosaurs like myself remember a time before object orientation?

Column me old fashioned, but I like my code compressed.

No comments:

Post a Comment