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

8 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

# Re: Find a list of words in multiple columns

Try this formula:

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

# Re: Find a list of words in multiple columns

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

# Re: Find a list of words in multiple columns

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

# Betreff: Find a list of words in multiple columns

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)

# Re: Find a list of words in multiple columns

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)

# Re: Find a list of words in multiple columns

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.

# Re: Find a list of words in multiple columns

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 # Re: Find a list of words in multiple columns

thank you all for help