Excel column label from integer

Question

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?

To clarify: if, say, the function is named spreadsheetColumn[], then spreadsheetColumn[27] should return "AA"? — J. M.'s missing motivation, Jun 23 '13 at 17:28
Related http://stackoverflow.com/q/2634427/353410 and http://stackoverflow.com/q/181596/353410 — Dr. belisarius, Jun 23 '13 at 17:34
@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, Jun 23 '13 at 18:01
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, Jun 23 '13 at 18:09
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, Jun 23 '13 at 18:30
The 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, Jun 23 '13 at 19:33
@Sjoerd: Thanks very much for this explanation, now it is clear that it can not be done easily with INtergerDigits. — Walter Schrabmair, Jun 24 '13 at 06:57

Theo Tiger · Accepted Answer · 2013-06-24T15:59:49.833

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 but FromExcelCol[spreadsheetColumn[702]] does not work. — Walter Schrabmair, Jun 23 '13 at 20:06
the correct solution for AAZ = 728 according Excels = COLUMN(AAZ). — Walter Schrabmair, Jun 23 '13 at 20:16
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, Jun 23 '13 at 20:20
@Tiger yes your solution is correct ! COngratulation. The Problem is tricky. Thanks a lot. — Walter Schrabmair, Jun 23 '13 at 20:22
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, Jun 23 '13 at 20:24
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, Jun 23 '13 at 20:34

score 5 · Answer 2 · answered Jun 23 '13 at 19:28

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

answered Jun 23 '13 at 19:28

kale

10,922
1
32
69

Also great! Thanks – Walter Schrabmair Jun 23 '13 at 19:56

Sjoerd C. de Vries · Answer 3 · 2013-06-23T21:16:45.680

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[
    Thread[
      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).

Jacob Akkerboom · Answer 4 · 2013-06-24T11:52:37.887

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

Try spreadsheetColumn[702]. I had I hard time getting this one right. — kale, Jun 23 '13 at 19:28
Indeed, I withdrew my vote as col 26 is incorrectly encoded as AZ — Sjoerd C. de Vries, Jun 23 '13 at 21:15
@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, Jun 23 '13 at 23:29
@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, Jun 24 '13 at 06:54
@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, Jun 24 '13 at 11:47
Jacob, I really like the Log part to determine the number of digits in the final column name! — Theo Tiger, Jun 24 '13 at 16:03
@TheoTiger thanks! I'm glad you do, I was a bit frustrated with myself. — Jacob Akkerboom, Jun 24 '13 at 16:22

score 1 · Answer 5 · edited Apr 13 '17 at 12:56

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;
   StringJoin@@(x/.Thread[Range[0,25]->CharacterRange["A","Z"]])
];

edited Apr 13 '17 at 12:56

Community

1

answered Jun 24 '13 at 14:33

faysou

10,999
3
50
125

Faysal, please take a look at Table[integerToLetters[iii], {iii, 500, 800}]. Can you fix this? – Jacob Akkerboom Jun 24 '13 at 16:23
I tried several similar things. Some of them you can find in the edit history of my answer. But I also tried something where I added one to the last element of "x", like you do. I could not figure it out though, but I am very curious if there is simpler way than my answer. Actually I was frustrated with myself when I saw how simple your answer was. Please don't go through the same frustration in trying to fix this :). – Jacob Akkerboom Jun 24 '13 at 16:29
True it doesn't work for some numbers, at least I know it now, thanks, I'll see if I can correct this, when I can. – faysou Jun 24 '13 at 21:39

Excel column label from integer

5 Answers5

Linked

Related