Debra at Contextures blogged about creating a word search puzzle while she nursed a hangover (although she won’t admit it). Creating a word search puzzle in Excel seemed like a fun exercise, so I set about doing it.
It’s not even close to finished, I’m just throwing the first draft out there to see what sticks. I have some design considerations.
The puzzle dimensions are set in A1 and the word list starts in A2. If a word is too big, you get an (currently unhandled) error. Perhaps I should allow the user to set the minimum puzzle size, but compute the correct puzzle size based on the word list. I haven’t thought about that algorithm much, but I think I’d start with Longest Word / 0.8. That would have to increase based on the number of long words. The more long words, the larger the puzzle will have to be to accommodate them all.
As it is now, if a word is greater than 80% of the puzzle dimensions I force the direction to be diagonal. If I don’t, the rest of the words use the same direction as the first word. For example, if SUMPRODUCT goes horizontally from row 4, column 1, then the odds are pretty good that the next three words will have to be either horizontal right or horizontal left. The only way they wouldn’t be horizontal is if, by luck, they meshed with SUMPRODUCT.
Any cells that aren’t part of a word are filled with a random letter. I’d prefer to fill some spaces with partial words, like MPROD, to make the puzzles harder. That will make the code run longer and it will increase the chance that I accidentally make a duplicate. I’d have to avoid that for small words, say five characters or less, which I think would make the odds of a duplicate almost nothing.
You can download the zip file if you want to check it out. If you have thoughts on how you’d design the puzzle, leave a comment.
Can’t you just use RANDBETWEEN(65,90)?
You could only use letters that appear in the words to fill in the blank cells. This would make the puzzles slightly harder, and may randomly add partial words into the puzzle. It would however increase the chance of randomly duplicating short words within the puzzle.
Jon – BXOJEV?
How about a way to “circle” the word easily once found?
Re circling a found word: break the paper-an-pencil paradigm and just color the cells in…
Doug –
#NAME?#NAME?#NAME?#NAME?#NAME?#NAME?
Oops! Forgot to install the ATP when I set up a new computer.
Jon – you’d better start using 2007 then :)
Doug – what has Jon ever done to you? ;)
I’m guessing he killed someone close to him to deserve that advice…
Fantastic – I will have great fun looking at your code!
I tried it out with a list of 95 Harry Potter names (max length 22, grid size = 22/0.8 = 27). The puzzle seemed to generate quite well but I noticed that while two of the names (Justrin-Finchley and You-Know-Who) contained hyphens there was only one hyphen in the grid. So not all the words are always included in the list and there is no indication of which words have been included and which not.
Which made me wonder how difficult it would be to enhance the code so that if you highlight any word in the list the corresponding entry in the grid is highlighted …
Replying to my own challenge – all you would need to do would be to store the initial cell and the direction and have these available in a dictionary or lookup table. Then you need a routine to highlight N cells given any starting cell and a direction.
Matthew: I can’t think why some would be excluded. Do you mind sending me the list? dkusleika@gmail.com
Oh, I think I should clean the words of any non-alpha characters like hyphens, don’t you think?
OK, I get it. I put a counter in there so that after 1,000 iterations, it would stop. You’re list of words is too long for a 27×27 grid and it quits after it can’t find space for them all. This is a good data point for the algorithm to determine optimum grid size. I wonder how that online place does it. Brute force would take too long.
I don’t follow how this would work in Excel. How would the solution lines work, or does this just create the letter grid?
This just makes the grid, you have to solve it yourself.