The yoursumbuddy official smartphone has two alarms. One wakes me up at the reasonable hour of 6:23, and one goes off at 9:35 every Sunday with a reminder that NPR’s Sunday Puzzle starts in three minutes. Last Sunday’s lent itself nicely to some Excel fun:
“Name two fictional characters – the first one good, the second one bad. Each is a one-word name. Drop the last letter of the name of the first character. Read the remaining letters in order from left to right. The result will be a world capital. What is it?”
To solve this, I wanted a list of villains and another of world capitals.
I recently realized that Excel’s Data > From Web feature is easier than copying stuff straight from the web. A lot of web lists have weird html formatting and this feature cuts past much of that. So I sucked in a list of villains from kaijuphile.com…
… and one of capitals from, that’s right, Wikipedia. Now down to work.
With both lists in a sheet, I added a couple of columns for each. For the villains, the first column gets rid of numbers and the word “The.” The 2nd strips out all villains with names longer than one word, per Mr. Shortz’s instructions. Here’s the formula:
There’s two fun things here:
1. The IfError part is formed backwards from its normal usage. We actually want the part that returns an error when it doesn’t find a space in the villain’s name: a one-word name.
2. However, this means that all the more-than-one-word villains will return a number – the location of the space. The Text part of the formula fixes that by returning blanks for numbers and leaving strings intact. For example Text(23,””) returns a blank, but Text(“twerp”,””) returns “twerp.” Hey, maybe the answer is Antwerp!
For the capitals, there’s just a bunch of columns, each one lopping off one more letter from the beginning. The villainous name forms the 2nd part of the capital, so if we’re lucky one of the froncated (front-truncated) strings will match a villain. The formula is:
Conditional formatting in column E rightwards turns a cell orange if it matches any of the villain names in column C. And, sure enough:
“Santiago” yields “Iago,” who as we all know, flew too near the sun and made a lot of people mad.
This means there’s a good fictional character whose name starts “Sant” followed by one more letter. Hmmm… I guess he wasn’t thinking of the Billie Bob Thornton version.