The Excel Word Challenge

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:

Spell1.gif

Andy Pope had the longest for a while with a six letter version of his name

Spell2.gif

But Andy didn’t make it to the annual IHOP Summit Outing, so he missed this 7-letter gem from John Walkenbach

Spell3.gif

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.

25 thoughts on “The Excel Word Challenge

  1. No work done today, but at least this exercise had Excel open! My word: DEFLATED (8 letters)

  2. 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.

  3. Aaarrrgghhh!!!!

    This is why we need more than 256 columns? My best so far is C-H-AF-ES.

    Aaarrrgghhh again!!!

  4. Wow, 9 letters so far… Excel MVPs sucked at this game

    And Dick, thanks for the coin ! Appreciate it.

  5. 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.

  6. 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!

  7. 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’]

  8. 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.

  9. I give up. You programmers have at it. But what a fun game/concept! Thanks to those MVPs who dreamt it up.

  10. 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?

  11. 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.

  12. 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.

  13. 50 lines? I gave up after writing about 20 lines, realizing that it was a lot more difficult than I originally thought.

  14. 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

    Option Explicit

    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

  15. 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

  16. Of course, (workWord “”) and (How “”) should be (hope it works this way) (workWord &lt&gt “”) and (How &lt&gt “”), respectively.

  17. Let’s get this started again with Excel 2007. Sixteen-thousand three-hundred and eighty-four columns! Woo hoo!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.