Wordle with Numbers (Part 2)

 In a previous post I explained how to use conditional highlighting in Numbers to make a Wordle-like game. It's simple enough for middle school students to create and have fun on their own and leaves open lots of extension opportunities. However, there are a few flaws in this clone: cheating can be rampant because each letter is checked immediately and individually so there's no waiting on submitting the entire word. Guesses are not limited to 6 since you can just erase a previous guess. There's no keyboard indicating which letters have been guessed or evaluated. Secret words must be manually updated, limited to one each game only, and cheaters could sneak peeks without anyone knowing.

Most of these issues could be overcome using advanced scripting like macros or visual basic (in Excel) but where's the fun in that? The whole rationale here is to clone something using a spreadsheet product to show students how formulas and spreadsheets work!

To hide the secret word a bit better and to create a word bank we can use a second sheet in Numbers, or a second worksheet in Excel/Google Sheets. Give that second sheet a name like "secret word list" and put a different word on each row, each letter separated into columns. In Column F insert the formula RAND and Numbers will assign a random number between 0 and 1.You can click the Column F menu arrow and sort by this column, thus creating a new random word to appear at the top of the list every time Numbers is open:

Now that we have a randomized word list we can reference the second table by updating our conditional highlighting rules. Back in table 1, open the rules by selecting the game area and clicking Format > Cell > Conditional Highlighting. Update the green rule first by typing in the first few letters of the second table name. When Numbers autofills the name press enter and type "$1$1" to reference the first row and double-check that "Preserve Row" is checked.

Update the remaining rules in a similar manner, referencing the correct columns and rows on the second sheet instead of the current sheet.

Forcing Numbers to check the letters only when an entire is submitted should be accomplishable by using Applescript but I haven't tried it. Also, the mobile app version of Numbers apparently has a Forms feature that creates fillable forms that may allow you to capture a word guess and then calculate the correct letters. You can also probably use text splitting formulas like =LEFT, =RIGHT, and =LEN to split the text into individual columns.

As a final thought, I'm intrigued by the possibilities of numbers in Numbers, ie cloning Nerdle. But this poses an interesting programming challenge where you not only have to provide correct integer placement clues, but also evaluate the validity of the equation! As with most programming solutions there are elegant and inelegant solutions and it's always interesting to see what students come up with.

Wordle with Numbers

Not Wordle the game with number as in integers (that game has already been made), but Wordle in Numbers the spreadsheet app!

This is one of those trendy projects ala Flappy Bird that will probably get outdated the moment it's published but it's still a nice introduction to spreadsheets, conditional formatting, and logic.

To recap the rules of Wordle:

• Guess a 5-letter word.
• White squares indicate unused guesses.
• Dark grey indicates that letter is not present in the secret word.
• Yellow indicates the letter is present but in an incorrect position.
• Green indicates the letter is present in the correct position.
• You have six guesses to guess the secret word.

Create a new Numbers spreadsheet. Type in a secret word in Row 1. Each letter should be in its own column:

The secret word is on row 1. Rows 2, 3, 4, 5, 6, 7 will contain our guesses. We can resize our playing area to make square cells and colour the borders by selecting our playing area: cell A2 to E7:

We can give rules to cells in spreadsheets. These rules can change data or the formatting of cells. We're going to have Numbers compare our guesses to the secret word and colour the cell if it's right or wrong.

Let's start with unused guesses. Unused guesses should be white. So, select our playing area of cells A2:E7 and click Format > Cell > Conditional Highlighting.

Add a rule that says Cell is blank and Color Fill it white:

Correct letter guesses that are also in the correct column position are coloured green. Highlight the playing area again (cell A2 to E7) and open Conditional Formatting again (Format > Cell > Conditional Highlighting) and add a rule that says Text is then click the cell reference button . Click on the number 1 in the first row to select the entire row:

Choose a green fill and make sure "Preserve Row" is selected. The rule should look like this, note the dollar signs that preserved the absolute reference to the cell:

Correct letters but in a wrong position are coloured yellow. So make sure the playing area is selected again and add a rule that says Text is and click the cell reference button . Select cell A1 to check against the first letter of the secret word and choose a yellow fill. Make sure "Preserve Row" and "Preserve Column" are selected:

Create five more rules that reference the next cells: B1, C1, D1 and E1. Make sure to enable "Preserve Row" and "Preserve Column" on each rule so it references the correct row and column with absolute references (the dollar sign $).

Now we can create a rule to show a dark grey if the letter guess is incorrect. With the playing area selected create another rules that says Cell is not blank and choose a custom colour fill of a dark grey colour:

Time to test! Enter a word in your playing area, one letter per cell. The cell colour should change to yellow if you have a correct letter. If it doesn’t work, time to debug or fix your code!

Common mistakes:

• Not applying the rules to all cells in your playing area
• Not including the "Preserve Row" or "Preserve Column" (the $ signs) in your formula rule, e.g. $A$1
• Extra spaces or punctuation

If the game is working then it's time to hide the secret word. Do this by right-clicking Row 1 and selecting Hide row

There are a few issues with hiding the secret word this way. Unscrupulous players can unhide the row and cheat very easily. (nevermind that they can cheat by having endless guesses, and they can even check letter by letter and not submit a whole word like real Wordle!). Also, the secret word needs to be changed manually each time you want to play. It would be nice to create a word bank of many words that get randomly selected for a game. That way, the game maker and player would have no knowledge of the word before playing and more than one game can happen. This kind of game can be created using multiple worksheets, and if I'll try to publish those steps in a separate post.