**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!

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?

Yep ToSafeInt is an extension method to convert the base class types to integer values.

ASCII tables show the values of the letters (http://en.wikipedia.org/wiki/ASCII). I’m glad you took a stab at it Aaron. I like little teasers like this to keep my brain moving.

That was definitely a good teaser. Thanks for posting it.

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

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.

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