Oct 06 2020 06:47 AM
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
Oct 06 2020 07:34 AM
SolutionTry this formula:
=IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")
Oct 06 2020 08:01 AM
It works. :)Would it be possible to select specific cells instead of the whole range of cells?
Oct 06 2020 08:22 AM
Just for some random cells, or do they have a pattern, for example every other or every third cell in A1:Q1?
Oct 06 2020 08:24 AM
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.
Nikolino
I know I don't know anything (Socrates)
Oct 06 2020 10:04 AM
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)
Oct 06 2020 12:10 PM
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.
Oct 06 2020 02:01 PM - edited Oct 06 2020 02:02 PM
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
Oct 06 2020 07:34 AM
SolutionTry this formula:
=IF(SUM(COUNTIF(A1:Q1,{"*white*","*blue*","*red*","*black*"})),"Yes","")