Excelcification: Brain Teaser Code

Problem: Someone at work recently asked me how you would go about converting excel header rows into integers. In the image below, you see each column and its corresponding integer value.

 

Thus, A = 0, B = 1, C = 2 and so on. Since I have no better word to describe this process, I’m going to call it Excelcification.

Def: Excelcification. The act of converting a alpha column into its numeric representation.

However, Excel cells go from A to Z and then become AA, AB, AC etc. So if Z = 25, AA = 26, AB = 27 and so on.

Your mission is to create a method that takes a string that corresponds to the excel column (don’t have to worry about spaces etc for now) and change that to its integer value.

 

In case you didn’t know, the largest column in Excel 2010 that I could see was XFD.

So what is the Excelcification of “XFD”?

 

[Jeopardy theme music plays…]

 

[Some time later…]

 

[Some more time later…]

 

Are you done? How did you do it?

 

My process was to recognize that this is going to be Base 26 arithmetic, also know as Hexavigesimal. But you didn’t need to know that. If you know how base 2 (binary) works, you can extrapolate it to work for base 26.  So how does base 2 work. If you remember your truth tables from high school/college, you would know that in binary:

01 = 2^0

10 = 2^1

11 = 2^1 + 2^0

 

Now extrapolate this for base 26 and realize that in binary, your digits are only 0 and 1 whereas in hexavigesimal, your digits are 0 through 25. (See the link there to A through Z).

Thus,

A = (26^0) * Numeric_Value(A)

B = (26^0) * Numeric_Value(B)

etc.

 

So if you create a method for this your code should be:

         public static class ExtensionMethods
    {
        /// <summary>
        /// Converts a string into its hexavigesical (base 26) representation.
        /// </summary>
        /// <param name="sxCol">Input string of letters.</param>
        /// <returns>-1 if input is null or empty, base 26 integer representation of input otherwise.</returns>
        public static int Excelcify(this string sxCol)
        {
            int result = -1;
            sxCol = sxCol.ToUpper();

            if (string.IsNullOrEmpty(sxCol))
                return result;


            for (int i = sxCol.Length; i > 0; i--)
            {
                char _c = sxCol[i-1];
                //
                // Function =>  (26 ^ reversed_char_index) * char_value
                //          A = 1 ------ Z = 26 ------ AA = 27 ------ AZ = 54
                // 64 because there 'A' starts at index 65 and we want to give 'A' the value 1.
                result += Math.Pow(26, sxCol.Length - i).ToSafeInt() *  (_c.ToSafeInt() - 64);
            }

            return result;
        }
    } 

And you would call your code as such:

 int iResult = “A”.Excelcify();
Debug.Assert(res == 0);
iResult = "Z".Excelcify();
Debug.Assert(res == 25);
iResult = "AA".Excelcify();
Debug.Assert(res == 26);

 

Thus “AAA” = 702, and “XFD” = 16383.

That is exactly 2^14 cells for those binary folk!

Advertisement