7
$\begingroup$

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?

|improve this question
$\endgroup$
  • 1
    $\begingroup$ To clarify: if, say, the function is named spreadsheetColumn[], then spreadsheetColumn[27] should return "AA"? $\endgroup$ – J. M. will be back soon♦ Jun 23 '13 at 17:28
  • $\begingroup$ Related stackoverflow.com/q/2634427/353410 and stackoverflow.com/q/181596/353410 $\endgroup$ – Dr. belisarius Jun 23 '13 at 17:34
  • $\begingroup$ @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}} &; $\endgroup$ – Walter Schrabmair Jun 23 '13 at 18:01
  • 3
    $\begingroup$ 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 + 0*26^1 + 0*26^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. $\endgroup$ – Sjoerd C. de Vries Jun 23 '13 at 19:33
  • 1
    $\begingroup$ Another link of potential interest: matlab implementation $\endgroup$ – Albert Retey Jun 24 '13 at 10:34
8
$\begingroup$

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.

|improve this answer
$\endgroup$
  • $\begingroup$ @Tiger, Thank you for the explanation. it gives me to think $\endgroup$ – Walter Schrabmair Jun 23 '13 at 19:58
  • $\begingroup$ @Tiger but FromExcelCol[spreadsheetColumn[702]] does not work. $\endgroup$ – Walter Schrabmair Jun 23 '13 at 20:06
  • $\begingroup$ the correct solution for AAZ = 728 according Excels = COLUMN(AAZ). $\endgroup$ – Walter Schrabmair Jun 23 '13 at 20:16
  • $\begingroup$ 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. $\endgroup$ – Theo Tiger Jun 23 '13 at 20:20
  • $\begingroup$ @Tiger yes your solution is correct ! COngratulation. The Problem is tricky. Thanks a lot. $\endgroup$ – Walter Schrabmair Jun 23 '13 at 20:22
5
$\begingroup$

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*)
|improve this answer
$\endgroup$
  • $\begingroup$ Also great! Thanks $\endgroup$ – Walter Schrabmair Jun 23 '13 at 19:56
4
$\begingroup$

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

|improve this answer
$\endgroup$
2
$\begingroup$

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

|improve this answer
$\endgroup$
  • $\begingroup$ Try spreadsheetColumn[702]. I had I hard time getting this one right. $\endgroup$ – kale Jun 23 '13 at 19:28
  • $\begingroup$ Great!!!! Thanks a lot! Walter $\endgroup$ – Walter Schrabmair Jun 23 '13 at 19:31
  • $\begingroup$ Congratulation, also for finding the bug. $\endgroup$ – Walter Schrabmair Jun 23 '13 at 19:55
  • $\begingroup$ Indeed, I withdrew my vote as col 26 is incorrectly encoded as AZ $\endgroup$ – Sjoerd C. de Vries Jun 23 '13 at 21:15
  • $\begingroup$ @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). $\endgroup$ – Jacob Akkerboom Jun 23 '13 at 23:29
1
$\begingroup$

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"]])
];
|improve this answer
$\endgroup$
  • $\begingroup$ Faysal, please take a look at Table[integerToLetters[iii], {iii, 500, 800}]. Can you fix this? $\endgroup$ – Jacob Akkerboom Jun 24 '13 at 16:23
  • $\begingroup$ 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 :). $\endgroup$ – Jacob Akkerboom Jun 24 '13 at 16:29
  • $\begingroup$ 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. $\endgroup$ – faysou Jun 24 '13 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.