IF the first word of one cell is found within the contents of another...return value A

[jeffcoleky]

=IF(A2="","EMPTY",(IF(ISNUMBER(SEARCH(A2,B2)),"Match","No-Match")))

I currently use the above formula to find out if the contents of cell A2 are included in the cell B2. It works well when there is only 1 word in A2. When there is more than one, it tries to match the entire cell and I need it to only match the 1st.

How do I change this to search for only the FIRST word of A2 in cell B2, and return "Match" if it is found, and "No-Match" if not?

Your help is much appreciated.

 

[CharlesChuckieCharles]

Not to put another question within this post, but would be easy to change this formula to search cell B2 for ANY word in cell A2, rather than just the first word of A2?

Two parts

VBA Macro

Public Function Findany(sLookupList As Range, sSearchStr As Range) As Long
Application.Volatile
Dim sLocalLookup As String
Dim sLocalSearchStr As String
Dim sTest As String
On Error Resume Next
Dim lMatches As Long
Findany = 0
lMatches = 0
sLocalLookup = Trim(UCase$(sLookupList.Value))
sLocalSearchStr = Trim(UCase$(sSearchStr.Value))

If sLocalLookup = "" Then
   Findany = -1
   Exit Function
End If
While Len(Trim(sLocalLookup)) > 0
   'extract search item from the left of the cell/string
   sTest = Trim(IIf(InStr(1, sLocalLookup, " ") > 0, Left(sLocalLookup, InStr(1, sLocalLookup, " ")), Trim(sLocalLookup)))
   'test for presence in search string
   If InStr(1, sLocalSearchStr, sTest) > 0 Then
      lMatches = lMatches + 1 ' if present
   End If
   'remove search items just checked
   sLocalLookup = Trim(Replace(sLocalLookup, sTest, ""))
   
   DoEvents
Wend
Findany = lMatches
Exit Function
End Function

and Function

=IF(Findany(A16,C16)=-1,"Empty",IF(Findany(A16,C16)>0,"Match","No-Match"))

 

[jeffcoleky]

Two parts

VBA Macro

Public Function Findany(sLookupList As Range, sSearchStr As Range) As Long
Application.Volatile
Dim sLocalLookup As String
Dim sLocalSearchStr As String
Dim sTest As String
On Error Resume Next
Dim lMatches As Long
Findany = 0
lMatches = 0
sLocalLookup = Trim(UCase$(sLookupList.Value))
sLocalSearchStr = Trim(UCase$(sSearchStr.Value))

If sLocalLookup = "" Then
   Findany = -1
   Exit Function
End If
While Len(Trim(sLocalLookup)) > 0
   'extract search item from the left of the cell/string
   sTest = Trim(IIf(InStr(1, sLocalLookup, " ") > 0, Left(sLocalLookup, InStr(1, sLocalLookup, " ")), Trim(sLocalLookup)))
   'test for presence in search string
   If InStr(1, sLocalSearchStr, sTest) > 0 Then
      lMatches = lMatches + 1 ' if present
   End If
   'remove search items just checked
   sLocalLookup = Trim(Replace(sLocalLookup, sTest, ""))
   
   DoEvents
Wend
Findany = lMatches
Exit Function
End Function

and Function

=IF(Findany(A16,C16)=-1,"Empty",IF(Findany(A16,C16)>0,"Match","No-Match"))

WoW. Thanks for taking the time to do that for me.

I put the VBA portion in the code of the SHEET? Then put the formula in the cell right?

 

[CharlesChuckieCharles]

Yes correct (this will find "Youn" in "Young" but not "Young1" in "Young")

so it may require twicking on how you want to handle spaces

possibly replace

If InStr(1, sLocalSearchStr, sTest) > 0 Then

with

If InStr(1, sLocalSearchStr, sTest & " ") > 0 Then ' ive add a space to the end of the test word

to enforce a trailing space

 

[jeffcoleky]

Yes correct (this will find "Youn" in "Young" but not "Young1" in "Young")

so it may require twicking on how you want to handle spaces

possibly replace

If InStr(1, sLocalSearchStr, sTest) > 0 Then

with

If InStr(1, sLocalSearchStr, sTest & " ") > 0 Then ' ive add a space to the end of the test word

to enforce a trailing space

It gives me a #NAME error. not sure how to troubleshoot this one..

 

[T. Valko]

=IF(A2="","Empty",IF(COUNT(SEARCH(LEFT(A2,FIND(" ",A2&" ")-1),B2)),"Match","No Match"))

That one worked as far as i can tell! thanks T. Valko

(charleschuckiecharles & JackDanIce, thanks for trying too)

You're welcome. Thanks for the feedback!

It looks like you've expanded your query!

 

[jeffcoleky]

You're welcome. Thanks for the feedback!

It looks like you've expanded your query!

Yes, but the solution to the expanded query isn't working, so I'm still using the one that works.

 

[CharlesChuckieCharles]

It gives me a #NAME error. not sure how to troubleshoot this one..

Has to be in a module but not thisworkbook or any sheet modules