Filtering Data - Contains formula

[ptaylor]

I have a list of internet search keywords and I want to filter out ones that have state or city in the keyword string. Is there any way to write a formula that references a list of states/cites and if any of state/city keywords appear in a keyword to have the referenced keyword appear in a cell?
Below example does not show a reference list of state/cities:

[TABLE="width: 424"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD="align: left"]Keyword String[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TD="align: left"]1031 exchange properties available in texas[/TD]
[TD="align: left"]texas[/TD]
[/TR]
[TR]
[TD="align: left"]1031 exchange facilitator washington state[/TD]
[TD="align: left"]washinton[/TD]
[/TR]
[TR]
[TD="align: left"]1031 exchange california primary residence[/TD]
[TD="align: left"]california[/TD]
[/TR]
[TR]
[TD="align: left"]1031 exchange rules california real estate[/TD]
[TD="align: left"]california[/TD]
[/TR]
[TR]
[TD="align: left"]1031 exchange rules real estate california[/TD]
[TD="align: left"]california[/TD]
[/TR]
</tbody>[/TABLE]

 

[=ODIN=]

There's probably a way to do this with array formulas, but those are not my strong suit.

However, here is a quick and dirty custom formula you can use if you like:

Function searchMultiple(searchCell As Range, keyWordRange As Range)
    searchMultiple = "NO KEYWORDS FOUND"
    
    Dim searchString As String: searchString = searchCell(1, 1).Value
    Dim keyWordArr: keyWordArr = keyWordRange
    




    For i = LBound(keyWordArr, 1) To UBound(keyWordArr, 1)
        For q = LBound(keyWordArr, 2) To UBound(keyWordArr, 2)
   
        If keyWordArr(i, q) <> "" And InStr(1, searchString, keyWordArr(i, q), vbTextCompare) <> 0 Then
            searchMultiple = keyWordArr(i, q)
            Exit Function
        End If
        Next q
    Next i


    
End Function

Add the code to a module in the workbook, and then use the formula would be =searchMultiple(cellAddress-withSearchString, rangeAddressOfKeyWords)

It is set just return the first keyword it finds. Keywords are not case sensitive.