Search for array of substrings in a string

BaraaKhalil · May 7th 2015

Hello everybody
I have the following UDF function that returns TRUE if one substring exists within another string

Code

Function Contains(strBaseString As String, strSearchTerm As String) As Boolean
    'Purpose: Returns TRUE if one string exists within another
    On Error GoTo ErrorMessage
        Contains = InStr(strBaseString, strSearchTerm)
    Exit Function
ErrorMessage:
    End
End Function

For example: The substring "Hello" exists in the string "Yes Hello everyone in this great forum"
So I can use this formula in B1

Code

=Contains(A1,"Hello")

and the result should be True according to the UDF function

Now I need to check more than substring .. say I need to check "hello" or "great" or "forum" .. If any substring found , the result should be True
Hope it is clear
My imaginaton about it

Code

=Contains(A1,{"hello","great","forum"})

MickG · May 7th 2015

Re: Search for array of substrings in a string

Perhaps:-

Code

=contains(A1,"hello,Great,Forum")

Code

Function Contains(strBaseString As Range, strSearchTerm As String) As Boolean
     'Purpose: Returns TRUE if one string exists within another
    Dim W As Variant
    For Each W In Split(strSearchTerm, ",")
        Contains = InStr(strBaseString, W)
        If Contains Then Exit Function
   Next W
End Function

jindon · May 7th 2015

Re: Search for array of substrings in a string

Code

Function Contains(strBaseString As String, CompareMode As VbCompareMethod, ParamArray a()) As Boolean
    Dim e
    For Each e In a
        If InStr(1, strBaseString, e, CompareMode) Then
            Contains2 = True: Exit For
        End If
    Next
End Function

=Contains(A1,1,"This","That","What")

1 for CompareMode 1 for TextCompare 0 for Binary compare(Case sensitive)
"This","That","What" = criteria, you can add as many as I think it was up to 64 separate by a comma.

BaraaKhalil · May 7th 2015

Re: Search for array of substrings in a string

Mr. MickG / Mr. Jindon
Thanks a lot for this great help and wonderful solution.

Can I use a range for the array?
I seek for using the array in range
In my imagination

Code

=Contains(A1,1,A1:A10))

A1:A10 will have the array values

Regards

apo · May 7th 2015

Re: Search for array of substrings in a string

Hi Jindon..

Quote

Contains2

Am I missing something..

Why append "2' to "Contains"?

BaraaKhalil · May 7th 2015

Re: Search for array of substrings in a string

Mr. apo
I recognized that missing but I didn't mention that

jindon · May 8th 2015

Re: Search for array of substrings in a string

Code

Function Contains(strBaseString As String, CompareMode As VbCompareMethod, ParamArray a()) As Boolean
    Dim e, r As Range
    For Each e In a
        If TypeOf e Is Range Then
            For Each r In e
                If Not IsEmpty(r) Then
                    If InStr(1, strBaseString, r.Value, CompareMode) Then
                        Contains = True: Exit For
                    End If
                End If
            Next
        Else
            If InStr(1, strBaseString, e, CompareMode) Then
                Contains = True: Exit For
            End If
        End If
        If Contains Then Exit For
    Next
End Function

Display More

=Contains(A1,1,$F$1:$F$3,"OK","No Good",$G$1:$G$100)

BaraaKhalil · May 8th 2015

Re: Search for array of substrings in a string

Thanks a lot Mr. Jindon for this magic UDF
I think the use will be three parameters only. Am I right?

Code

=Contains(A1,1,$F$1:$F$3)

Generally I tested it and it works very well. Thank you very much.

jindon · May 8th 2015

Re: Search for array of substrings in a string

Correct and it is just showing how it should be used when needed to do like that.

Tom.Jones · May 8th 2015

Re: Search for array of substrings in a string

Quote from jindon;745415

Code

Function Contains(strBaseString As String, CompareMode As VbCompareMethod, ParamArray a()) As Boolean
    Dim e, r As Range
    For Each e In a
        If TypeOf e Is Range Then
            For Each r In e
                If Not IsEmpty(r) Then
                    If InStr(1, strBaseString, r.Value, CompareMode) Then
                        Contains = True: Exit For
                    End If
                End If
            Next
        Else
            If InStr(1, strBaseString, e, CompareMode) Then
                Contains = True: Exit For
            End If
        End If
        If Contains Then Exit For
    Next
End Function

Display More

=Contains(A1,1,$F$1:$F$3,"OK","No Good",$G$1:$G$100)

Jindon how can I use this function?
What should be in F1:F3 and G1:G100

apo can you show me some example of text to use that UDF?
I ask you cause you're online.

Thanks.

jindon · May 8th 2015

Re: Search for array of substrings in a string

Both F1:F3 and G1:G100 should contain string to search.(criteria)

?

BaraaKhalil · May 8th 2015

Re: Search for array of substrings in a string

Mr. Jindon
I just have one range of strings in F1:F3 ..and I used the formaul

Code

=Contains(A1,1,$F$1:$F$3)

and it works well

jindon · May 8th 2015

Re: Search for array of substrings in a string

As I mentioned, every argument after 3rd will be optional, so no need to add if you don't need to.

Search for array of substrings in a string

Participate now!

Share