WordXLe: Wordle in Excel

If you play Wordle daily, or the French version LeMot, you might want to practice more often. For fun, I created an Excel version that you can download, WordXLe, and a Wordle Solver. It has sheets for both English and French versions. It requires an Excel 365 subscription because it uses the dynamic array functions. If you don’t have Excel 365, you can still use the Google sheets version of the Wordle Helper. Be sure to Save a Copy so you can edit the copy.

The workbooks were last updated 27 April, 2022. Be sure you have the latest version! UPDATE 9 Feb 2022: BYROW function and LAMBDA make the formulas more manageable.

UPDATE 3 May 2022: Wordle Helper 4.0 released. (Downloads: http website, insecure or Dropbox download, secure.) This requires the latest Excel 365 version because it uses the BYROW and LAMBDA functions. It uses the same grid layout as Wordle so should be more familiar. You set the colour of a cell by clicking its spinner. The colour scheme is configurable. Let me know if you find it better!

Watch the video of my talk on this topic to the London Excel Meetup.

The dictionaries are:
English main (for validation) c.12000 words from the SOWPODS dictionary; for play c.1100 words.
Version française: principal c.8000 mots; https://github.com/hbenbel/French-Dictionary/blob/master/dictionary/dictionary.txt

Le jeu 1700 mots. https://www.freelang.com/dictionnaire/dic-francais.php
I removed all accents to simplify the game.

It uses Conditional Formatting for colouring, Data Validation to enforce some letter entry rules, no VBA macros, just formulas. The sheets are protected, but it’s easy to unhide things in Excel if you really want to so I’ll leave that as a challenge. 

CAUTION: Always ensure that this is the first workbook you open after starting Excel, and that you close without saving and exit Excel when finished the game. This workbook uses iteration to randomly select a hidden word. If any other workbook is open before this is opened, that will probably prevent this from working. If Excel gives a warning about circular references when you complete the first word, click File > Options > Formulas and check “Enable iterative calculation”. When finished, close this workbook without saving it, and close Excel, to avoid the iterative calculation setting of this workbook affecting any subsequent ones you open.

How to play: Enter one letter per cell, one valid 5-letter word per row. This version recognises about 12000 words but only 1100 are in the selection for playing. The cells change colour, following the Wordle scheme for colour-blind vision: Orange: correct letter in the correct place. Blue: a letter in the word but not in the correct place.  Grey: incorrect letter. Do not change a word once entered. Work down the rows until you get them all correct. If you want some help, click the “Show Hints” checkbox to see a list of words (max 100) that fit your letters so far. If you cannot do it on the sixth row,  the answer will appear in the right hand column.

Wordle Helper

By popular demand I give a Wordle helper workbook for Excel 365 with sheets for both English and French. Right-click the link and download it. Or, use my Google Sheets Wordle Helper; save a copy so you can edit your own copy.

On the language sheet you want, enter the letters you know so far and it will show you the words that fit that selection. Handy for when you have only one guess left! Here is how it works:

Think of this as a text searching problem that could be used in other scenarios. You might have part codes or account codes where there may have been transcription errors and you want to find out what the entry should have been.

We start with a list of known words. Wordle has a list of 12970 words. I use a list of12478.
In the game, we know three things about the letters in each of your guess words:
1) Those in the correct place in the unknown word; they are on green tiles.
2) Those present in the unknown word but not in the correct place; they are on yellow.
3) Those not in the unknown word; they are on grey.

So the Helper has to match the words in the complete word list with the letters know to be in or not in the word.
In Excel 365, we have the FILTER() function which dynamically filters a list. It is not in Excel 2019, 2016 or older.
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
=FILTER(range, criteria, ifempty)
The range is our list of words, for example in W1:W12478.
The criteria expression tests each word and returns a True to include it in the filtered list, False to exclude it.
So we could show all words beginning with “A” like this:
=FILTER(W1:W12478, LEFT(W1:W12478,1)="A","")
The final “” parameter is for the function to return an empty string rather than #CALC!

From now on, I’ll use the range name “en” to refer to our English dictionary list.

Let’s set up five cells and place the known correct letters in each of the five cells.
Suppose we have five cells A2:E2 as shown in the picture above.
Our test for the exact correct first letters then is
=FILTER(en,LEFT(en,1)=A2,””)

Now to test two letters. We want the filter to include only words where both letters are in the right place.
You might think we could use AND( LEFT(A1,1)=A2 , MID(en,2,1)= B2)
But unfortunately AND does not work with arrays. =AND({TRUE,TRUE},{TRUE,FALSE}) returns only one result, FALSE, not the expected {TRUE, FALSE}
The way to combine them with AND is to multiply the two tests. Multiplication returns a numeric result where 1=True, 0=False.
={TRUE,TRUE}*{TRUE,FALSE} returns {1,0}
So our formula now is (I’ve changed the LEFT to MID for consistency)
=FILTER(en, (MID(en,1,1)=A2) * (MID(en,2,1)=B2) ,””)

However, if either A2 or B2 is blank, that will fail, as a letter does not match a blank.
So we extend the test by allowing any cell to be empty.

The SEARCH function does a case-insensitive search of one string inside another.
=SEARCH(“a”,”start”) returns 3
If you search for an empty string, it always returns 1.
=SEARCH(“”,”start”) returns 1
If you search for a string NOT in the searched string, it returns an error value.
=SEARCH(“x”,”start”) returns #VALUE!
Therefore to get a simple True/False, we use ISNUMBER() around the search so any numeric result of the search is True and any error is False. We multiply all the search results together as they must all be true for this first set. We only need one ISNUMBER because search with an empty cell always returns 1. To make such a long formula easier to read, press Alt+Enter after each subexpression so it finally reads

=FILTER(en,ISNUMBER(    
SEARCH(A2,MID(en,1,1))  
* SEARCH(B2,MID(en,2,1))  
* SEARCH(C2,MID(en,3,1))  
* SEARCH(D2,MID(en,4,1))  
* SEARCH(E2,MID(en,5,1))  ),"") 

It would be nice to make a subarray search of that, but you need the BYROW or BYCOL functions only released 9 Feb 2022. And here it is!

=FILTER(en,BYROW( 
 ISNUMBER(SEARCH(A2:E2,MID(en,SEQUENCE(1,5),1))),
 LAMBDA(array,PRODUCT(--array))))

Secondly we want to filter words containing letters that we know are in the target word, although not necessarily in the position of our guess.  We put all these letters into one cell, G2. There cannot be more than five of these letters. The following formula returns all the words containing the first letter of G2.

 =FILTER(en,ISNUMBER(SEARCH(MID(G2,1,1),en))) 

We want to apply this filter to the output of the previous filter. Rather than make the first formula even longer, we’ll create a new formula and its input range is the output of the first filter. If the first filter is in I3, then its spilled dynamic range is referred to as I3#.

=FILTER(I3#,ISNUMBER(SEARCH(MID(G2,1,1),I3#))) 

Now to extend the formula to cover up to five letters in G2. We put this in J3:

=FILTER(I3#,ISNUMBER(
 SEARCH(MID(G2,1,1),I3#)
*SEARCH(MID(G2,2,1),I3#)
*SEARCH(MID(G2,3,1),I3#)
*SEARCH(MID(G2,4,1),I3#)
*SEARCH(MID(G2,5,1),I3#)),"")

With BYROW() based on J3# above :
=FILTER(I3#,BYROW(ISNUMBER( 
 SEARCH(MID(G2, SEQUENCE(1,5),1),I3#)),
 LAMBDA(array,PRODUCT(--array))))

Finally we consider the letters which are NOT in the hidden word. There could be at most 21 of these, but let’s take a sensible limit at 15. We can put them all into one cell, say H2.
We want to exclude any word containing any of these letters. In other words, we include any word where the search for the letter returns an error. we use ISERROR instead of ISNUMBER.

A complication is that ISERROR(SEARCH(MID(H2,1,1),J3#)) will return False if H2 is empty, because SEARCH will return 1.
So we ensure the test string is padded to at least fifteen characters as follows:
In K2, put =H2&REPT(“@”,15) and now use K2.
Put this in L2, using the filter result from J3.

=FILTER(J3#,
 ISERROR(SEARCH(MID(K2,1,1),J3#))
*ISERROR(SEARCH(MID(K2,2,1),J3#))
... *ISERROR(SEARCH(MID(K2,15,1),J3#)),"")

Version with BYROW() :
=FILTER(J3#, BYROW(
 ISERROR(SEARCH(MID(K2, SEQUENCE(1,15),1),J3#)),
 LAMBDA(array,PRODUCT(--array))
))

Now we have the complete filter result.

See the workbook “WordleHelper.xlsx“. The intermediate columns I:K are hidden for tidiness but the sheets are unprotected. The BYROW version is not uploaded yet, until BYROW is generally available to all Excel 365 users. You have the formulas above.

Here is Joe McDaid’s tweet on the release of 9-Feb-2022:

Today we rolled out lambda function support to Excel users on the current channel. If you use dynamic arrays, I’d recommend starting with BYROW and BYCOL which can be used to aggregate rows/columns and spill the results.
Announcing LAMBDAs to Production and Advanced Formula Environment, A Microsoft Garage Project

https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambdas-to-production-and-advanced-formula/ba-p/3073293

Let me know how you like it!

Advertisement