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.

 image

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.

image

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!

About these ads
Tagged , , , , ,

6 thoughts on “Excelcification: Brain Teaser Code

  1. Aaron Wood says:

    I assume that the ToSafeInt() method does the conversion of letters to an int? I.e. if you put ‘C’ into the ToSafeInt method, you get back 3?

  2. Aaron Wood says:

    Nevermind, I see that the math function handles characters apparently.

  3. Aaron Wood says:

    Oh wait. Now I see. Char has an index value, but it starts at 65 (for ‘A’). Wheee, I finally get it now. It’s like I’m back at my interview, lol.

    Nice, elegant solution. Mine was not looking quite so elegant, lol.

  4. [...] Excelcification: Brain Teaser Code « Aboutdev's Weblog [...]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: