Excel- Search Cell for more than one value

[mushynator]

Does anyone know if there is a way to apply a search such that you search for more than one value in a cell. For example, if I have a workbook, and within that workbook I know or think that there is a cell that contains the words "A" and "B" but they don't appear "A B". They could appear like "A something B". So I guess I am looking for a function that searchs for the values "A" AND "B" appearing in one cell. Does anyone know how to do this?

[Bob Phillips]

Just do a standard find for say A, and then when you find a cell, check if it also contains B, if not find next and so on.

[mushynator]

So you are saying to search for "A" and then visually check to see if that cell also contains "B"? That is actually what I am doing now, however, I have a workbook with about 500 rows and 30 columns of data and there is way too many cells that contain "A" so it would take a very long time to visually inspect each cell that also contains "B" at the same time. I was hoping to learn about a function in excel that can automate this for me. I think I will have to write my own macro to do this kind of custom search. Thanks for the help though!

[Bob Phillips]

No, not visually, in code. Something like this



Dim c As Range
Dim FirstAddress As String
Dim NoMore As Boolean

With ActiveSheet.Range("a1:a500")
Set c = .Find("A", LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
If InStr(c, "B") = 0 Then
Set c = .FindNext(c)
Else
NoMore = True
End If
Loop While Not c Is Nothing And Not NoMore And c.Address <> FirstAddress
End If
End With