Thursday, October 6, 2011

Excel Column Sequence Algorithm

One of my friend came up with a question..."How do you find out the sequence of MS Excel column ?", lets put this in an another way, If I tell you one Excel Sheet column sequence e.g. ABA how do you find out the index of the column e.g. AA is 27th column, AB is 28th column.

So, I thought to put this as an Algo; with this if you have any sequence, you will be able to compute the column number-

Column Number = Alphabet Seq Number (right most) + Alphabet Seq Number * 26 + Alphabet Seq Number * (26*26) + .......... + Alphabet Seq Number (left most) * (p-1 times multiplication of 26)

Where,
Alphabet Seq Number = 1-26, (A = 1 and Z = 26)
p = number of Alphabets in the Column Sequence e.g. for "ABC", p = 3. Here  is C at position 1, B at 2 and A at 3.

So, with the above formula, Column number for ABC = C + B * 26 + A * (26 * 26)  
= 3 + 2 * 26 + 1* (26*26) = 731

So, what about doing the reverse i.e. generate the sequence from column number. To do this, we need to divide the number by 26 and consider the reminders to pick the Alphabet. The sequence of reminders that we get from 731 are 3 (C), 2(B), 1(A), so the Column sequence is "ABC" (last reminder value at the first).

Please find the CalculateExcelColumn.java to generate Column sequence from Column number. Hope the Algo helps...:-)

1 comment: