CODEWITHSUNDEEP
excelstringsearchsubstringcontains
rdulantoc
rdulantoc

Reputation: 44

Find exact substring in cell using excel formula

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.

enter image description here

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

Answers (3)

Scott Craner
Scott Craner

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

enter image description here

Upvotes: 2

urdearboy
urdearboy

Reputation: 14590

For anyone viewing question & open to VBA UDF solution

Post 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

basic
basic

Reputation: 11968

The FILTERXML function can help in this case:

=SUMPRODUCT(--(B3=FILTERXML("<data><a>" & SUBSTITUTE($F$2;"+";"</a><a>") & "</a></data>";"//a")))

enter image description here

Upvotes: 1

Related Questions