Excel can be used for many useful tasks. This isn’t one of them. The challenge is to find the largest word that can be spelled using Excel’s column headers by hiding columns. One example would be COT:
Andy Pope had the longest for a while with a six letter version of his name
But Andy didn’t make it to the annual IHOP Summit Outing, so he missed this 7-letter gem from John Walkenbach
There are two ways to win: Find the largest word that can be spelled in this way or write the shortest VBA code or worksheet formula that verifies that a word can be spelled in this way. If you win, you get recognized on Daily Dose of Excel, the web’s premier Excel blog with nearly 20 visitors a day! Good luck and try to get some work done today.
No work done today, but at least this exercise had Excel open! My word: DEFLATED (8 letters)
Too much work done today. What an odd break this was. Fortunately, my word is already on my mind.
BARBECUED
so is
BEER
but it’s not nearly long enough.
Aaarrrgghhh!!!!
This is why we need more than 256 columns? My best so far is C-H-AF-ES.
Aaarrrgghhh again!!!
Here’s 9 letters (I’m sure there’s something longer)
M AC AD AM IA
“accelerative”
Wow, 9 letters so far… Excel MVPs sucked at this game
And Dick, thanks for the coin ! Appreciate it.
damn.. only 256 columns? I guess ‘VE’ is no good then.
All right, I admit it, I don’t even have a copy of Excel. But I’m a sucker for word games that can be solved with a little C code.
“adamancies”. 10 letters. That’s the longest in the official scrabble dictionary.
E-L-IC-IT
To call forth, draw out, or provoke – as in, to provoke me into playing this EXCELlent word game instead of working. DOH!
ahh… there was a bug in my program. Oops.
P S Y CH ED EL IC
It’s only eight letters, but they are all single-letter column names:
A-E-G-I-L-O-P-S
http://dictionary.reference.com/search?q=aegilops
I too wrote software to do this, but not in VBA or Excel. It was fun.
The longest word I found was BACTERICIDIN [‘BA’, ‘CT’, ‘ER’, ‘IC’, ‘ID’, ‘IN’], which made it the only 12 letter long word. There were seven 11 character long words, such as ANOEGENETIC.
It’s interesting that SUBTERFUGE can be spelt in this manner, too. [‘S’, ‘U’, ‘BT’, ‘ER’, ‘FU’, ‘GE’]
BACTERICIDIN?
Damn. Your dictionary is bigger than mine.
Good job Lao. I found 2-12 letter words and 5-11 letter words, and bactericidin was one of them. I just need a bigger dictionary. The one I used was from http://www.orchy.com/dictionary/ What did you use, Shan and Lao?
It looks like JWalk’s getting the secret prize for only using single-letter columns. No way someone will beat that.
I’ll post the results in a few days.
I give up. You programmers have at it. But what a fun game/concept! Thanks to those MVPs who dreamt it up.
We owe it all to the pitchers of “Red” at Rock Bottom. And, BTW, “Red” is a valid Excel column word. But “Excel” is not. Ironic?
I was using the scrabble long word list. Good thing I never tried to use “bactericidin” while playing scrabble.
Anyway, I tried some other dictionaries, and came up with:
CERAMBYCIDAES
…beetles of the family cerambycidae. Not exactly a common word, but hey, google finds two entomologists that use it.
Shen, this is awesome!
I can’t beat either the longest word or the single column word :(
But I can add a twist which will let EX-CE-L work.
It requires XL2003 (maybe even XL2002), on the Tools > Options dialog on the International tab is a switch that allows right-to-left layout.
This opens up a lot of new letter combinations :0
What do you think Dick, imaginative work-around or cheat?
BTW my 2 checking VBA routines are just short of 50 lines.
50 lines? I gave up after writing about 20 lines, realizing that it was a lot more difficult than I originally thought.
I have posted a page on my site so if anybodys interested they can see the code. I’m sure its not 100% complete.
http://www.andypope.info/fun/Columnwords.htm
I hadn’t seen this thread till it got spammed (now removed)
I used a RegExp approach which generated some more 11 letter words but I couldn’t top CERAMBYCIDAES with the dictionary I used
wavelengths
psychedelia
interethnic
My identifying/testing code may be of interest. I used two very long regular expressions to parse 55,000 words and test for a valid match. I’m 99.9% sure that the validation works …. the concern is a string with a single / double column overlap may pass through incorrectly. If anyone finds one please let me know
Cheers
Dave
Sub CheckWords()
‘Requires a reference to MicroSoft VBscript Regular Expressions 5.5
‘This can an be done programatically with
‘ActiveWorkbook.VBProject.References.AddFromGuid “{3F4DACA7-160D-11D2-A8E9-00104B365C9F}”, 5, 5
Dim RegEx As regexp
Dim ColPos As Range
Dim NewString As String, TestWord, tWord As Variant
TestWord = Array(“BEER”, “BARBECUED”, “MACADAMIA”, “ELICIT”, “PSYCHEDELIC”, “SUBTERFUGE”, _
“AEGILOPS”, “BACTERICIDIN”, “ANOEGENETIC”, “CERAMBYCIDAES”, “NOTME”)
Set RegEx = New regexp
‘Create test string of “(A)?(B)?(C)?……(AA)?(AB)?(AC)?…..(IV)?
RegEx.Pattern = “(w+):w+”
For Each ColPos In Sheets(1).Columns
NewString = NewString & “(” & RegEx.Replace(ColPos.Address(, False), “$1”) & “)?”
Next
RegEx.Global = False
‘Tests ABCD….AAABAC…IV
For Each tWord In TestWord
RegEx.Pattern = NewString
If RegEx.Replace(tWord, 1) = “1” Then
Debug.Print tWord & ” is valid”
‘A second test is necessary as the first test may incorrectly partially match a string
‘ie “BACTERICIDIN” matches “B” then “AC” leaving no match for “TE” or “T”
Else
‘Create test string of “(AA)?(AB)?(AC)?……(IV)?
‘ie “BACTERICIDIN” matches “BA” then “CT” then “ET” etc
RegEx.Pattern = Right(NewString, Len(NewString) – InStr(NewString, “(AA)”) + 1)
‘Tests AAABAC….IV
If RegEx.Replace(tWord, 1) = “1” Then
Debug.Print tWord & ” is valid”
Else
Debug.Print tWord & ” is invalid”
End If
End If
Next
Set RegEx = Nothing
End Sub
Hi All,
Here’s my function to do the test. The cool thing is, instead of just a Boolean, it returns a string showing which columns to use, or “” if the combination isn’t possible. With no recursion, no references needed, and it only has 14 lines of non-blank code in the body!
=====
Function ColSpellPoss(ByVal oneWord As String) As String
workWord = oneWord
For ColNum = 256 To 1 Step -1
ColAddr = Columns(ColNum).Address(False, False)
ColName = Left(ColAddr, InStr(ColAddr, “:”) – 1)
If ((Len(workWord) > 1) And (Right(workWord, 2) = ColName)) Then
workWord = Left(workWord, Len(workWord) – 2)
How = ColName + ” ” + How
ElseIf (Right(workWord, 1) = ColName) Then
workWord = Left(workWord, Len(workWord) – 1)
How = ColName + ” ” + How
End If
Next ColNum
If (workWord “”) Then How = “”
ColSpellPoss = Trim(How)
End Function
=====
I set this up with a “Check” button in cell B2 with the following assigned macro.
=====
Sub CheckWord()
‘ Where are the important items?
WordSheet = “Sheet2?
WordCell = “C2?
‘ Get the word
oneWord = UCase(Trim(Worksheets(WordSheet).Range(WordCell).Text))
‘ Check the word
How = ColSpellPoss(oneWord)
‘ Report results
If (How “”) Then
MsgBox How, , “Possible”
Else
MsgBox “Not Possible”, , “Not Possible”
End If
End Sub
=====
Charlene
charmtg at yahoo dot com
Of course, (workWord “”) and (How “”) should be (hope it works this way) (workWord <> “”) and (How <> “”), respectively.
:( Well, I think you get the idea anyway.
Let’s get this started again with Excel 2007. Sixteen-thousand three-hundred and eighty-four columns! Woo hoo!