Victor Momoh in the Global Excel Summit, to start a discussion on readability, provocatively used MMULT in a formula to determine whether a word contains all five vowels.
As Victor said, in real life he’d use a simple SUM:
=SUM(1*ISNUMBER(SEARCH(MID(“aeiou”,SEQUENCE(1,5),1),A2)))
SEQUENCE generates {1,2,3,4,5}. The MID returns an array of each letter in “aeiou”. As Rick Rothstein pointed out, the formula would be even simpler with that array written out literally:
=SUM(1*ISNUMBER(SEARCH({“a”,”e”,”i”,”o”,”u”},A2)))
The SEARCH checks where the letter occurs in the word in A2 and returns a number if present or #VALUE! if not. ISNUMBER converts that to a True or False. 1* converts them to 1/0. SUM adds the array up so if it’s 5, then all five vowels are present (eg in “sequoia”).
To use this as a filter for a list of words in A2:A30000 :
=FILTER(A2:A300000,BYROW(A2:A300000,LAMBDA(word,5=SUM(1*ISNUMBER(SEARCH(MID(“aeiou”,SEQUENCE(1,5),1),word))))))
The FILTER function outputs a dynamic array of the rows in a given range that match a condition.
https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
If column B contains numbers, to get a list of the values in column A where B is greater than 3, use =FILTER(A2:A100,B2:B100>3). Because this is a dynamic array function, you only need to enter it once and its output expands to the number of matching values.
To filter for words containing one letter, we can use =FILTER(A2:A30000,ISNUMBER(SEARCH(“a”,A2:A30000)))
However we can not use an array eg {“a”,”e”} for the SEARCH first argument as that would generate a two dimensional array which would not fit the one dimension that the filter requires. You can verify that by entering =SEARCH({“a”,”e”},A2:A30000) in a cell. We want to restrict the scope of the search to each word, each row of the words list. To do that we use the BYROW function.
https://support.microsoft.com/en-us/office/byrow-function-2e04c677-78c8-4e6b-8c10-a4602f2602bb
=BYROW(array, lambda(row)) applies a LAMBDA to each row and returns an array of the results.
https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67
A LAMBDA is a user defined function in Excel, it does not involve VBA. The function we use takes one argument, given the arbitrary name “word” and that word is searched for each vowel in turn and the sum of the successful searches is calculated.
To make this easier to read, I’ll modify the formula above, which uses LAMBDA directly (that’s called an ‘anonymous’ function) to use a named function UniqueVowelCount() which we define ourselves by creating a Name as follows:
=UniqueVowelCount(“Excel”) returns 1, there is one distinct vowel “e” in the word, twice.
So now we can use BYROW to pass each row of A2:A30000 to the UniqueVowelCount function to create a one-column array of vowel counts and pass the result of comparing each with 5 to the FILTER function
=FILTER(A2:A300000,BYROW(A2:A300000,LAMBDA(word,5=UniqueVowelCount(word))))
Or, if you prefer,
FILTER(A2:A300000, 5 = BYROW(A2:A300000, LAMBDA(word, UniqueVowelCount(word))))
which you can evaluate using the Formula Auditing tab, Evaluate Function button.
P.S. Not that anyone asked, but in the SOWPODS corpus (now called the Collins Scrabble (TM) word list) there are 2,932 words of less than 15 letters with all five vowels.
Patrick O'Beirne @ sysmod 
Please explain.
In my first draft, I stopped at the first mention of the FILTER function. I have now added an explanation. Let me know if it could be clearer!
Main difference is the use of AND rather than SUM. Plus some stylistic change.
= LET(
vowels, {“a”,”e”,”i”,”o”,”u”},
allvowels?, BYROW(list,
LAMBDA(word,
AND(ISNUMBER(SEARCH(vowels, word)))
)
),
FILTER(list, allvowels?)
)
Thanks,Peter. Can you show how that would be used?
First, you have a typo in the real-life formula that Victor would use… it is missing a closing parentheses at the end. Second, this shorter formula (two less function calls) appears to return the same count number as Victor’s real-life formula does…
=SUM(0+ISNUMBER(SEARCH({“a”,”e”,”i”,”o”,”u”},A2)))
First an observation if anyone wishes to experiment with some of these formulas; the smart double quotes need to be replaced before Excel will accept them. In use, my formula also expects the word list to be within a named range “list”.
I have no problem with your use of MID to extract the vowels, since I know the result I just chose to express it as an array constant (1985 technology rather than 2018). Had I followed your formula I would probably used the Lambda function Explodeλ for the purpose.
= Explodeλ(“aeiou”)
where Explodeλ is defined to be
= MID(string, SEQUENCE(LEN(string)), 1)
The key element of the formula is your expression
= ISNUMBER(SEARCH(vowels, word))
which returns a Boolean array. Wrapping in AND requires All to be present whereas OR would require one or more distinct vowels to be present.
I do regret not naming the Lambda function, as you later did, either within the LET as a local variable or within the AFE. The formula could then be collapsed to
= FILTER(list, BYROW(list, AllVowelsλ))
where AllVowelsλ is defined to be
= AND(ISNUMBER(SEARCH(vowels, word)))
This would have been pretty compact, but unlike the traditional spreadsheet formula writing, I do not normally aim at brevity.
Brian Canes adds:
=SUM(COUNTIF(A2,”*”&MID(“aeiou”,{1,2,3,4,5},1)&”*”))=5