*Sorry for not testing previous versions well. This version works*

I am not sure if I am making things way more complicated than needed. The code below defines the function you want, `spreadsheetColumn`

. It is extremely crazy code in which the results of some calculations as well as some coding techniques are densely packed. But the resulting definition will be quite compact and I guess that could be our measure of how simple this solution is.

```
ClearAll[spreadsheetColumn, chR]
(*note that n does not need to be cleared*)
chR = CharacterRange["A", "Z"];
With[
{numberOfIntegerDigitsPlusOne := Ceiling[Log[26, 26 + 25*n]]
,
numberInTuples := (676 - 26^#)/650 + n}
,
SetDelayed @@ Hold[
spreadsheetColumn[n_],
StringJoin@
Part[chR, 1 + IntegerDigits[numberInTuples - 1, 26, # - 1]] &@
numberOfIntegerDigitsPlusOne
]
]
```

examples

```
spreadsheetColumn[26^2 + 4 26 + 3]
```

"ADC"

```
spreadsheetColumn[27]
```

"AA"

```
spreadsheetColumn[728]
```

"AAZ"

**Explanation**

*Warning: There is quite a lot to explain, so I have not filled in all the details (nor have I formatted everything very well)*

Here is a version that is not "packed". It should also work for all bases, not just 26 (although I guess you have to have to have a sensible definition for chR for `base>=26`

).

```
n = 26*26 + 26 + 1;
base = 26
numberOfIntegerDigits = Ceiling[Log[base, base - n (1 - base)] - 1];
numberInTuples = n - (base - base^numberOfIntegerDigits)/(1 - base)
(*not so nice, we generate a bunch of useless tuples*)
Tuples[Range[numberOfIntegerDigits],
numberOfIntegerDigits][[numberInTuples]]
(*nice alternative*)
charReps =
1 + IntegerDigits[numberInTuples - 1, base, numberOfIntegerDigits];
StringJoin@Part[chR, charReps]
```

"AAA"

**How it works**

We can make 26 columns using one character

We can make 26^2+26 columns using up to two characters

Generalizing this, we can make

```
Sum[26^i, {i, m}}] == (26-26^(m+1))/(1-26) == (26^(m+1)-26)/25
```

columns using n characters.

We can find out how many characters we need to represent our number `n`

by finding the largest `m`

, `m*`

, such that `n >= (26^(m+1)-26)/25`

. Let `f[m_]:=(26^(m+1)-26)/25`

. Then `m* = Ceiling[(f^-1)[n]]`

, where by `^-1`

I mean the inverse function (`InverseFunction`

). This turns out to be `Ceiling[Log[26, 26 + 25*n]]-1`

.

Then we want to subtract `(26^(m*+1)-26)/25`

from our original number, which is the index of our desired combination of characters, in all combinations of m* characters.

I found a nice way to do this last step using `IntegerDigits`

, which I guess I was aiming for all along.

**Remark on whether this approach is "too complicated"**

Note that in a usual number system there are base^n numbers represented by up to n numbers. Our characters really correspond to another number system. I am not sure if things can be done much easier for this reason.

*This was kind of a silly exercise, but after all the failed attempts I just had to get a solution :)*

1To clarify: if, say, the function is named

`spreadsheetColumn[]`

, then`spreadsheetColumn[27]`

should return`"AA"`

? – J. M.'s ennui – 2013-06-23T17:28:04.100Related http://stackoverflow.com/q/2634427/353410 and http://stackoverflow.com/q/181596/353410

– Dr. belisarius – 2013-06-23T17:34:37.467@J. M., yes, could this be right in this way? myexcelcol = {Characters[IntegerString[ #, 26]] /. {"0" -> A, "1" -> B, "2" -> C, "3" -> D, "4" -> E, "5" -> F, "6" -> G, "7" -> H, "8" -> I, "9" -> J, "a" -> K, "b" -> L, "c" -> M, "d" -> N, "e" -> O, "f" -> P, "g" -> Q, "h" -> R, "i" -> S, "j" -> T, "k" -> U, "l" -> V, "m" -> W, "n" -> X, "o" -> Y, "p" -> Z}} &; – Walter Schrabmair – 2013-06-23T18:01:17.960

It does not work properly. What do I wront. @belisarius I need this fnc in Mathematica, and I can not find it in your ref – Walter Schrabmair – 2013-06-23T18:09:12.357

the inverse fnc of the wanted fnc is:at@MyExcelStartPos where [Code] (

Transformation from Excel "A1" notation to indexes) at = {FromDigits@ ToExpression@Flatten[StringCases[Characters[#], _?DigitQ], 1], FromDigits[ Flatten[ToCharacterCode /@ StringCases[Characters[#], _?UpperCaseQ] - First@ToCharacterCode["A"] + 1, 2], Length@CharacterRange["A", "Z"]]} &; [/Code] – Walter Schrabmair – 2013-06-23T18:30:35.4573The difficulty in Excel column naming is the lack of a zero. Counting starts at 'a' (1), goes to 'z' (26), then to 'aa' (27). In a normal base 26 system with 26 symbols, the first one ('a' in this case) would have value 0 and the last one ('z') 25. 'ba', the smallest two digit number, would then be 26, and 'baa', the smallest three digit number, 1* 26^2 + 0

26^1 + 026^0 = 676. Unfortunately, in Excel the smallest two-digit number is 'aa' which means that 'a' in a way acts as both a zero and a one. – Sjoerd C. de Vries – 2013-06-23T19:33:05.963@Sjoerd: Thanks very much for this explanation, now it is clear that it can not be done easily with INtergerDigits. – Walter Schrabmair – 2013-06-24T06:57:32.163

1

Another link of potential interest: matlab implementation

– Albert Retey – 2013-06-24T10:34:08.883