Find a list of words in multiple columns

[temporalnaut]

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

[#2 · Accepted solution]

@temporalnaut 

Try this formula:

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

[#3]

@Hans Vogelaar 

It works. :)Would it be possible to select specific cells instead of the whole range of cells?

[#4]

It works. :)Would it be possible to select specific cells instead of the whole range of cells?

[#5]

@temporalnaut 

Just for some random cells, or do they have a pattern, for example every other or every third cell in A1:Q1?

[#6]

@temporalnaut 

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)

https://support.microsoft.com/en-us/office/check-if-a-cell-contains-text-case-insensitive-7bb505c7-2...

Nikolino

I know I don't know anything (Socrates)

[#7]

@Hans Vogelaar 

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)

[#8]

@temporalnaut 

That is much more complicated. The only way I can think of is to store the list of colors in a range, and to use a custom VBA function:

Function FindText(rng As Range, ParamArray args()) As String
    Dim c As Variant
    Dim c1 As Range
    Dim w As Variant
    Dim s As String
    Dim v As String
    For Each c In args
        If TypeName(c) = "Range" Then
            For Each c1 In c
                v = " " & c1.Value & " "
                For Each w In rng
                    If InStr(1, v, " " & w.Value & " ", vbTextCompare) Then
                        s = s & vbLf & Trim(v)
                        Exit For
                    End If
                Next w
            Next c1
        Else
            v = " " & c & " "
            For Each w In rng
                If InStr(1, v, " " & w.Value & " ", vbTextCompare) Then
                    s = s & vbLf & Trim(v)
                    Exit For
                End If
            Next w
        End If
    Next c
    If s <> "" Then
        FindText = Mid(s, 2)
    End If
End Function

Let's say that you entered the words white, blue, red and black in A3:A6, and that you want to return the values of the cell(s) in A1, C1, F1, H1:K2, D2 and L1:P1 that contain at least one of those words (matching whole words only).

The formula would be

=FindText(A3:A6,A1,C1,F1,H1:K2,D2,L1:P1)

Turn on Wrap Text for the cell with the formula.

Make sure that you save the workbook as a macro-enabled workbook (.xlsm) and that you allow macros when you open the workbook.

[#9]

@temporalnaut 

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

[#10]

thank you all for help