Forum Discussion
Find a list of words in multiple columns
Hi, please, I use this formula in Excel to find out which cells contain certain text:
=IF(COUNT(SEARCH({"white","blue","red","black"},B1)),"Yes","")
As you can see, the formula looks for text in cell B1. How to extend the search field to the range of cells A1:AQ1? Because when I write A1:AQ1 there, it looks like nothing was found. Thank you
Try this formula:
=IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")
8 Replies
This solution uses the Excel 365 function LET to organise the solution but defined names or substituting the local variables out to get a single nested formula would work as well:
= LET( words, {"white";"blue";"red";"black"}, matrix, SIGN(ISNUMBER(SEARCH(words,array))), sum, MMULT({1,1,1,1}, matrix), IF(sum,"Yes","-") )Only the first and last rows in the image are needed, the rest serves as explanation
In addition to the actually best solution that Mr.Hans Vogelaar (@Hans Vogelaar) offered you, this information. But could also use Mr. Hans Vogelaar (@Hans Vogelaar) formula, just need to set the area to a specific cell.
Check if a cell contains text (case-insensitive)
Nikolino
I know I don't know anything (Socrates)
Try this formula:
=IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")
Just for some random cells, or do they have a pattern, for example every other or every third cell in A1:Q1?
