Reputation: 44
I have the string "L5+L6+L7+L10" in a cell, and then I have a column with the values L1, L2, ... , L10 on each cell. I need to know which of those values are contained in the main string but with an exact match.
To solve my problem, I tried the formula =IF(ISNUM(SEARCH(B3;$F$2));"Found";"Not found")
and the results I get are shown in this image.
However, this results are incorrect because I need only L5, L6, L7 and L10 to be found, and not L1.
Is there a way to do so only using excel formulas?
Upvotes: 1
Views: 1674
Reputation: 152660
Just add the "+"
to the beginning and end of the strings inside the search:
=IF(ISNUMBER(SEARCH("+"&B3&"+","+"&$F$3&"+")),"Found","Not found")
Upvotes: 2
Reputation: 14590
UDF
solutionPost code into a worksheet module
Syntax of formula: =Found( Substring, Search String )
So on the excel sheet: C3 = Found(B3, F2) = Not Found
Public Function Found(SubString As Range, Within As Range) As String
Dim Arr, i As Long
Arr = Split(Within, "+")
Found = "Not Found"
For i = LBound(Arr) To UBound(Arr)
If SubString = Arr(i) Then
Found = "Found"
Exit For
End If
Next i
End Function
Upvotes: 0
Reputation: 11968
The FILTERXML
function can help in this case:
=SUMPRODUCT(--(B3=FILTERXML("<data><a>" & SUBSTITUTE($F$2;"+";"</a><a>") & "</a></data>";"//a")))
Upvotes: 1