## Excel column label from integer

7

1

I would like to create a function in Mathematica, which returns the equivalent Excel column label (e.g., A, Z, AA, AZ, and so on) given a column number.

I think it can be done with IntegerString[10,26], but this representation set of this chars is 0 to 9 and then a to z. However, Excel uses only A to Z.

How can this be done?

1To clarify: if, say, the function is named spreadsheetColumn[], then spreadsheetColumn[27] should return "AA"? – J. M.'s ennui – 2013-06-23T17:28:04.100

– 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.457

3The 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 + 026^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

8

Ok, well, I only needed the inverse function so far and have implemented it as:

FromExcelCol[col_String] := FromDigits[ToCharacterCode[col] - 64, 26]


It runs fine because FromDigits does not complain about characters larger than base-1.

However, the other way round seems to be more tricky. The leading digit runs from 0 to 26 (1 to 27 if you want -> base 27), but only as long as it is leading. Then it runs as a trailing digit from 1 to 26.

I have not found an elegant, non-iterative solution so far. I was hesitating to paste my (ugly but working) piece of code, but maybe it encourages others to look for a nice solution. :)

ToExcelCol[n_Integer] := Module[{subtract, num, base},

subtract = Accumulate[Power[26, #] & /@ (Range[7] - 1)];
base = Position[subtract, x_ /; x > n, {1}, 1][[1, 1]] - 1; (*find largest number which is <= base ... I think there are better alg. for this but I don't have them at hand, sorry*)
num = n - subtract[[base]];

StringJoin@PadLeft[FromCharacterCode /@ (IntegerDigits[num, 26] + 65), base, "A"]
]


Basically, what you have to do and what this function does, is subtract 26^0 if n > 26^0, then subtract 26^1 if n > 26^1+26^0 and so on. Finally pad the result to x digits with the largest 26^x+26^(x-1)+....

I have tried but I could not make a satisfying solution, so I kindly invite you to improve this piece of code. I still find that it is too iterating (with the lookup table generated in the first step). I have also thought of treating part of the number as base 27 and the other part as base 26, but well... not tonight anymore. :)

EDIT: Jacob just figured out what I was looking for: You can determine the number of digits in the final column name with Log[26, 25 (n + 1)]. So, here is the simplified version:

ToExcelCol2[n_Integer] := Module[{num, base},
base = Floor[Log[26, 25 (n + 1)]];
num = n - Total[Power[26, #] & /@ (Range[base] - 1)];
StringJoin@PadLeft[FromCharacterCode /@ (IntegerDigits[num, 26] + 65), base, "A"]]


You can condense this into a one-liner without Module of course, but I'll leave that up to you.

@Tiger, Thank you for the explanation. it gives me to think – Walter Schrabmair – 2013-06-23T19:58:51.787

@Tiger but FromExcelCol[spreadsheetColumn[702]] does not work. – Walter Schrabmair – 2013-06-23T20:06:46.043

the correct solution for AAZ = 728 according Excels = COLUMN(AAZ). – Walter Schrabmair – 2013-06-23T20:16:54.137

Yep. For me, FromExcelCol["AAZ"] returns 728. Inversely, ToExcelCol[728] returns "AAZ". spreadsheetColumn does not work correctly for me.

This: FromExcelCol /@ ToExcelCol /@ Range[20000] == Range[20000] returns True, so the functions are the correct inverse of each other, empirically. – Theo Tiger – 2013-06-23T20:20:20.213

@Tiger yes your solution is correct ! COngratulation. The Problem is tricky. Thanks a lot. – Walter Schrabmair – 2013-06-23T20:22:46.680

Yes, but anyway I still do not like the code. kale's recursive version is much better in terms of simplicity. However, I am still looking for a good non-iterative (and non-recursive) variant. – Theo Tiger – 2013-06-23T20:24:49.040

Yes Kale works also fine. Maybe you will find a non interative or non recursive solution. Let me and the community know. thanks – Walter Schrabmair – 2013-06-23T20:34:38.110

5

Here's a recursion method:

toexcelcolumn[col_?IntegerQ] /; col < 27 := FromCharacterCode[col + 64]
toexcelcolumn[0] = "Z";
toexcelcolumn[col_?IntegerQ] :=
toexcelcolumn[Quotient[col - 1, 26]] <> toexcelcolumn[Mod[col, 26]]

toexcelcolumn[13935]

(*TOY*)


Also great! Thanks – Walter Schrabmair – 2013-06-23T19:56:05.337

4

I once developed a function for precisely this problem but I couldn't find at the moment (probably somewhere at the office).

I thought I'd do it this time with a (not-so-elegant) function that takes a bit of preparation, but is much faster once set up. Nice if you have to do numerous look-ups (I once used Mathematica to analyze a 70,000 cell Excel sheet, building a graph of cell relationships. Graphs are a nice visualization tool to study spreadsheets).

numToExcel =
Dispatch[
Range[16710] ->
DeleteCases[
(Characters[IntegerString[#, 27]] /.
Thread[IntegerString[Range[0, 26], 27] -> {"."}~Join~CharacterRange["A", "Z"]]
//StringJoin) & /@
Range[18000],
_?(\[Not] StringFreeQ[#, "."] &)]]];


What this basically does is building a base 27 set of consecutive numbers, using the "." to represent 0 and A to Z representing 1..26. I then throw away all number strings that contain "." (ugly, isn't it?), so leaving me with all the consecutive Excel column names. These are then put in a rules Dispatch table so that I can say:

16384 /. numToExcel


XFD

This is about 7 times faster than Jacob's solution, which on its turn is about 10 times faster than kale's. (I admit this is cheating).

2

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[

StringJoin@
Part[chR, 1 + IntegerDigits[numberInTuples - 1, 26, # - 1]] &@
numberOfIntegerDigitsPlusOne

]
]


examples

spreadsheetColumn[26^2 + 4 26 + 3]


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 :)

Try spreadsheetColumn[702]. I had I hard time getting this one right. – kale – 2013-06-23T19:28:56.430

Great!!!! Thanks a lot! Walter – Walter Schrabmair – 2013-06-23T19:31:26.690

Congratulation, also for finding the bug. – Walter Schrabmair – 2013-06-23T19:55:17.160

Indeed, I withdrew my vote as col 26 is incorrectly encoded as AZ – Sjoerd C. de Vries – 2013-06-23T21:15:53.380

@SjoerdC.deVries I think I still like the solution using IntegerDigits. However, I think it will a geometric series, strangely. This is turning out to be an interesting problem :) (but also a little frustrating). – Jacob Akkerboom – 2013-06-23T23:29:36.903

@Jacob: Yes it is not correct, maybe Jacob could find a non recorive solution of this problem with INtegerDigits. That would be great. – Walter Schrabmair – 2013-06-24T06:54:53.700

@WalterSchrabmair Well, this is something. I hope somebody likes it :P. Also I am curious if things can be done easier. I always felt like I was so close with my previous versions.. – Jacob Akkerboom – 2013-06-24T11:47:24.220

Jacob, I really like the Log part to determine the number of digits in the final column name! – Theo Tiger – 2013-06-24T16:03:23.757

@TheoTiger thanks! I'm glad you do, I was a bit frustrated with myself. – Jacob Akkerboom – 2013-06-24T16:22:58.013

@Jacob, thanks a lot. Great done! – Walter Schrabmair – 2013-09-02T14:36:37.567

1

Another variant that I wrote here https://mathematica.stackexchange.com/a/3905/66

integerToLetters[n_]:=
Module[{x},
x=IntegerDigits[n-1,26];
x[[;;-2]]-=1;

Faysal, please take a look at Table[integerToLetters[iii], {iii, 500, 800}]. Can you fix this? – Jacob Akkerboom – 2013-06-24T16:23:27.123