Forum Discussion

Copper Contributor

Oct 06, 2020

Solved

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 ce...

excel

Formulas and Functions

HansVogelaar
Oct 06, 2020
temporalnaut

Try this formula:

=IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")

HansVogelaar

MVP

Oct 06, 2020

temporalnaut

Try this formula:

=IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")

temporalnaut
Copper Contributor
Oct 06, 2020
HansVogelaar
It works. 🙂Would it be possible to select specific cells instead of the whole range of cells?
- HansVogelaar
  MVP
  Oct 06, 2020
  temporalnaut
  
  Just for some random cells, or do they have a pattern, for example every other or every third cell in A1:Q1?
  - temporalnaut
    Copper Contributor
    Oct 06, 2020
    HansVogelaar
    Without a pattern. I need to choose this, this and this cell (and then, of course, after dragging the formula to the next row, the values corresponding to the respective row are recalculated).
    I have two more questions:
    1) how to modify this formula or what formula should be used to display the text (s) it found instead of "Yes" (the mark that the cell contains the found text).
    2) how to search only for whole specific words (not parts of other words, eg "bluegrass" to ignore when searching for blue)