# Excel column label from integer

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"? Jun 23, 2013 at 17:28
• Jun 23, 2013 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}} &; Jun 23, 2013 at 18:01
• 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. Jun 23, 2013 at 19:33
• Another link of potential interest: matlab implementation Jun 24, 2013 at 10:34

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

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 Jun 23, 2013 at 19:56

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

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
Jun 23, 2013 at 19:28
• Great!!!! Thanks a lot! Walter Jun 23, 2013 at 19:31
• Congratulation, also for finding the bug. Jun 23, 2013 at 19:55
• Indeed, I withdrew my vote as col 26 is incorrectly encoded as AZ Jun 23, 2013 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). Jun 23, 2013 at 23:29

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? Jun 24, 2013 at 16:23