Every time I hear Will Shortz say “name a country of the world” my heart beats a little faster, cause I know there’s probably an array-formula post in it. This week was no exception:

“Name an article of clothing that contains three consecutive letters of the alphabet consecutively in the word. For example, “canopy” contains the consecutive letters N-O-P. This article of clothing is often worn in a country whose name also contains three consecutive letters of the alphabet together. What is the clothing article, and what is the country?”

This spreadsheet is live. Double-clicking in a cell in the 2nd column shows the formula. You can then drag the lower-right fill handle to see the whole gnarly thing.

Countries are a lot more list-friendly, so I solved that part first. There are two types of solutions above: an array formula in the 2nd column, and a conditional formatting solution in the 3rd to 40-somethingth column. The 2nd solution is the way I’d do it for serious work, but the array formula is more fun:

This formula says to compare every letter in the country name to the next two letters. If the numeric code for a letter is one less than the letter after it, and two less than the letter after that, return those three letters. The reason the 2nd column is mostly blank is because surprisingly few country names have three consecutive letters. (Or my formula sucks.)

Here’s how it works. Let’s assume the formula refers to Albania, the 3rd country in the list:

LEN returns the length of the country’s name.

INDIRECT(“1:”& LEN($A4)-2) evaluates to (1:5).

ROW(INDIRECT(“1:”& LEN($A4)-2)) evaluates to {1;2;3;4;5}. This tells the array formula which characters in “Albania” to evaluate against their respective two next characters. Using ROW this way is very handy in array formulas.

CODE(MID(LOWER($A4),ROW(INDIRECT(“1:”& LEN($A4)-2)),1)) resolves to {97;108;98;97;110}. In other words, the numeric values of the first five letters of Albania are 97, 108, etc. CODE is the function that returns the values. LOWER converts each letter to lower case, otherwise, for example, a lowercase “b” wouldn’t be seen as following an uppercase “A”. The MID piece tells the array formula to parse each of the first 5 letters in the word.

Okay, now we’re getting close. This bit:
CODE(MID(LOWER($A4),ROW(INDIRECT(“1:”& LEN($A4)-2)),1))+1=
CODE(MID(LOWER($A4),ROW(INDIRECT(“2:”& LEN($A4)-1)),1)) resolves to
{98;109;99;98;111}={108;98;97;110;105}, which in turn resolves to {FALSE;FALSE;FALSE;FALSE;FALSE}. In other words, one added to the code for the first five letters in “Albania” doesn’t ever equal the code for their respective next letters. So there’s not even two consecutive letters. What a country! The next two lines in the formula repeat the comparison, this time with the 2nd through 7th letters.

The 2nd line, which starts with MID, and the 2nd to last line say that if the whole mess is true, return the three letters that start at that position.

The IFERROR function at the beginning and the last line simply say that if the formula errors (doesn’t find three consecutive letters) put a blank in the cell. IFERROR is available in Excel 2007 and 2010. I like it very much as they keep formulas like this from being twice as ridiculously long.

Much of the evaluation above was done using the F9 key. Selecting part of a formula and then pressing F9 evaluates that piece of the formula, a very useful feature.

There are only two countries that meet the criteria, Afghanistan and Tuvalu. I figured it was probably the former and soon enough realized the article of clothing was “hijab.”

If you’ve made it this far, thanks! Oh yeah, the multiple cell and conditional formatting way works similarly to the array formula, only by breaking it up it’s a lot easier.

If anybody has a better array formula, please share.