How do I fix my macro to search for a pattern within a string in excel?

The below code creates a function that searches for a list of values in a string, but it returns all of the values found.  I only want it to return with the first value found.
Public Function FindMatches(Test_Cell As Range, Lookup_array As Range) As String
    'This returns all substring matches within a cell
    Dim cl As Object
    For Each cl In Lookup_array
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            FindMatches = FindMatches & cl.Value & ", "
        End If
    Next cl
    If FindMatches <> "" Then
        FindMatches = Left(FindMatches, Len(FindMatches) - 2)
    Else
        FindMatches = ""
    End If
End Function

Open in new window

kbay808Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rgonzo1971Connect With a Mentor Commented:
Hi,

pls try

Public Function FindMatches(Test_Cell As Range, Lookup_array As Range) As String
    'This returns all substring matches within a cell
    FindMatches = ""
    Dim cl As Object
    For Each cl In Lookup_array
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            FindMatches = cl.Value
            Exit For
        End If
    Next cl
End Function

Open in new window

Regards
0
 
Pratima PharandeConnect With a Mentor Commented:
Public Function FindMatches(Test_Cell As Range, Lookup_array As Range) As String
    'This returns all substring matches within a cell
    Dim cl As Object
    Dim flag As Boolean
    flag = False
     
    For Each cl In Lookup_array
    If flag = False Then
        If InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
           FindMatches = FindMatches & cl.Value & ", "
         
         flag = True
        End If
       
        End If
    Next cl
    If flag = True Then
   
   
FindMatches = Left(FindMatches, Len(FindMatches) - 2)
End If

End Function
0
 
kbay808Author Commented:
Rgonzo1971/Pratima Pharande
Both of your codes returns with the last result found in the string.  I need the first result and then stop looking.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Rgonzo1971Commented:
Example pls
0
 
Rgonzo1971Commented:
Maybe

Public Function FindMatches(Test_Cell As Range, Lookup_array As Range) As String
    'This returns all substring matches within a cell
    FindMatches = ""
    Pos = 10000000
    Dim cl As Object
    For Each cl In Lookup_array
        If cl <> "" And InStr(1, Test_Cell, cl.Value, vbTextCompare) > 0 Then
            Tmp = cl.Value
            PosTmp = WorksheetFunction.Find(Tmp, Test_Cell.Value)
            If PosTmp < Pos Then
                Res = cl.Value
                Pos = PosTmp
            End If
        End If
    Next cl
    FindMatches = Res
End Function

Open in new window

Regards
0
 
kbay808Author Commented:
Both of your solutions do work.  For some reason it was not working with my test data set, but they worked just fine for the rest.  Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.