Clean algorithm to get Excel column letters from column index

Asked Nov 13 '15 at 22:24

Active Nov 13 '15 at 22:44

Viewed 88 times

This is trickier than it looks at first blush.

In Excel, columns have letter identifiers:

1st column is "A"
2nd is "B"
...
26th is "Z"
27th is "AA"
28th is "AB"
...
"ZY"
"ZZ"
"AAA"
...

I am looking for the cleanest algorithm (i.e. the most readable) that can turn the column number into the column identifier.

I explicitely don't want a solution that works inside Excel. It needs to run standalone.

I have my solution, but am looking for other approaches.

Here are my (NUnit) test cases. They are correct, to the best of my knowledge.

    [TestCase(1, "A")]
    [TestCase(26, "Z")]
    [TestCase(27, "AA")]
    [TestCase(52, "AZ")]
    [TestCase(53, "BA")]
    [TestCase(702, "ZZ")]
    [TestCase(703, "AAA")]
    [TestCase(18278, "ZZZ")]
    [TestCase(18279, "AAAA")]
    [TestCase(475255, "AAAAA")]

edited Nov 13 '15 at 22:30

pnuts

58,317
11
87
139

asked Nov 13 '15 at 22:24

Cristian Diaconescu

34,633
32
143
233

`=LEFT(ADDRESS(1, 28, 2), FIND("$", ADDRESS(1, 28, 2))-1)` where 28 is the column. – Nov 13 '15 at 22:29
1

@Jeeped OP doesn't want excel solution. – findwindow Nov 13 '15 at 22:29
It was offered as a starting point for whatever language the OP ultimately decides upon. – Nov 13 '15 at 22:30
1

Is [this what you are looking for](http://stackoverflow.com/q/21633017/335858)? – Sergey Kalinichenko Nov 13 '15 at 22:33
@MacroMan In any language - just don't slap an Excel built-in function. The test cases use NUnit's syntax, but I recon they're readable enough regardless of used framework – Cristian Diaconescu Nov 13 '15 at 22:33
@pnuts yeah, I just found that question too... – Cristian Diaconescu Nov 13 '15 at 22:35
1

@pnuts Funny that I didn't see it as a suggestion while writing the question, but it showed up in the list of related questions. – Cristian Diaconescu Nov 13 '15 at 22:39

Clean algorithm to get Excel column letters from column index

0 Answers0