Notice the emphasis here is on words, not substrings.
Once we have the words in an array, they can be tested.
Note: The TEXTSPLIT function is only available in the most recent version of Excel.
We can count words, compare words, check for specific words, etc.
The solution is to expand the list of delimiters provided to TEXTSPLIT to include punctuation when needed.
This is also bad, although, depending on the use case, it may not matter.
We now have the core process we need to begin testing a cell for specific words.
In the worksheet shown, we are looking for the word “green”.
There are different ways to go about this in Excel.
For example, we could count instances of the word “green” in the array returned by TEXTSPLIT.
However, a more scalable approach is to use theXMATCH functionto check the result from TEXTSPLIT.
What happens if we check for a non-existent value, for example, the word “pink”?
When XMATCH does not find a value, it will return an #N/A error.
The only remaining task is to return TRUE or FALSE.
So, when XMATCH returns a number, COUNT will return a positive number.
When XMATCH returns an error, COUNT will return zero.
To get a TRUE or FALSE result we can check to see if the count is greater than zero.
We now have all the pieces we need to test for specific words in a cell.
“), a comma (”,") and a space (" “).
We also setignore_emptyto TRUE by providing 1.
This is an important detail.
Enabling the ignore empty behavior will remove any empty values that creep into the output from TEXTSPLIT.
How should we adjust the formula to handle more than one lookup value?
The second #N/A error indicates that “blue” was not found.
TheCOUNT functiononly counts numbers so it returns 1 and the final result is TRUE.
Note: this formula will return TRUE if any number of words is found.
We use COUNTA to count the words because COUNTA will count both numbers and text values.
To make the formula more efficient and easier to read, we’ll add theLET functionto the mix.
The idea is thatxwordscontains words that we want to exclude.
If either expression is FALSE, the result will be FALSE.
TEXTSPLIT can split text into rows or columns.
XMATCH Function
The Excel XMATCH function performs a lookup and returns apositionof a value in a range.
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
…
COUNT Function
The Excel COUNT function returns a count of values that are numbers.
Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers.
COUNTA does not count empty cells.
LET Function
The Excel LET function lets you define named variables in a formula.
