User defined function to extract specific string

[YasserKhalil]

Hello everyone
I need UDF that extracts the string that exists in an array
Example :
a=Array("UY","OP","ST")

example of string : "Hello everyone ST exists here" >> expected output would be ST (case sensitive)

Another example : "Hello everyone ST exists here UY too" >> expected output would be UY

Hope that clear
Regards

 

[Hui]

Do you need a Function?
What about a formula?

 

[YasserKhalil]

Thanks a lot for reply Mr. Hui
In fact I need that as it is a part of large code so UDF is the best solution for me

 

[NARAYANK991]

Hi ,

Something like this :

Public Function RetrieveMatch(inputrange As Range) As Variant
                Const LOOKFOR = "UY,OP,ST"
                Dim LookForArray As Variant, currpos As Variant
                Dim i As Integer, maxpos As Integer
                Dim matchfound As Boolean
                Dim lookin As String
              
                lookin = " " & inputrange.Value & " "
                LookForArray = Split(LOOKFOR, ",")
                matchfound = False
              
                For i = 0 To UBound(LookForArray)
                    currpos = InStr(1, lookin, " " & LookForArray(i) & " ", vbBinaryCompare)
                    If Not VBA.IsError(currpos) Then
                        If currpos > maxpos Then
                          maxpos = currpos
                          RetrieveMatch = LookForArray(i)
                        End If
                        matchfound = True
                    End If
                Next
              
                If Not matchfound Then RetrieveMatch = CVErr(0)
End Function

Narayan

 

[YasserKhalil]

Thank you very much Narayan for this great and awesome solution
Best Regards