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 :)
spreadsheetColumn[]
, thenspreadsheetColumn[27]
should return"AA"
? $\endgroup$ – J. M.'s ennui♦ Jun 23 '13 at 17:28