find a complex string in a substring in VBA

Question

how can I get in VBA a complex string in a substring, e.g. if i = InStr("test-VBA", " this is a test") then msgbox "a part of searching Item exist"

with function "Instr" didn't work because the seraching word is "test-VBA" and ofcourse dosen't

exist as a one word but what I search for if a complete part of the searching item ("test" in the

example as part of "test-VBA") exists should I get a msgbox like described above

Thanks a lot.

use split to split this is a test to an array, and then check all members of the array individually. — Luuk
– Luuk, Commented Jun 25, 2022 at 8:50

Mik · Accepted Answer · 2022-06-26 06:50:05Z

0

Function IsInStr_IgnoreCase(ByVal Str As String, ByVal Value As String) As Boolean
Dim objRegEx as Object
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.IgnoreCase = True
    objRegEx.pattern = Value
    IsInStr_IgnoreCase = objRegEx.test(Str) ' objRegEx.test(Str) returns True if Sustr exists.
End Function


Sub test()

' this returns TRUE if you get a match
MsgBox IsInStr_IgnoreCase_AsBoolean("CHINA-Country", "Move the dev from China to Newseeland")

' this returns all matches of 'Value' in 'Str'
MsgBox IsInStr_IgnoreCase_AsString("CHINA-Country", "Move the dev from China to Newseeland")

' you need to adjust objRegEx.Pattern if you need to get a specific match. See RegEx.
End Sub


Function IsInStr_IgnoreCase_AsBoolean(ByVal Value As String, ByVal Value As String) As Boolean
Dim objRegEx As Object
Dim tStr, tVal, iStr, iVal
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Global = True
    objRegEx.Pattern = "(\b\w+)|(\b\d+)"
    Set tStr = objRegEx.Execute(Str)
    Set tVal = objRegEx.Execute(Value)
    objRegEx.IgnoreCase = True
    For Each iStr In tStr
        For Each iVal In tVal
            objRegEx.Pattern = iVal
            If objRegEx.test(iStr) Then
                IsInStr_IgnoreCase_AsBoolean = True
                Exit Function
            End If
        Next
    Next
End Function


Function IsInStr_IgnoreCase_AsString(ByVal Str As String, ByVal Value As String) As String
Dim objRegEx As Object
Dim tStr, tVal, iStr, iVal
    Set objRegEx = CreateObject("VBScript.RegExp")
    objRegEx.Global = True
    objRegEx.Pattern = "(\b\w+)|(\b\d+)"
    Set tStr = objRegEx.Execute(Str)
    Set tVal = objRegEx.Execute(Value)
    objRegEx.IgnoreCase = True
    For Each iStr In tStr
        For Each iVal In tVal
            objRegEx.Pattern = iVal
            If objRegEx.test(iStr) Then
                IsInStr_IgnoreCase_AsString = IsInStr_IgnoreCase_AsString & iStr & "; "
            End If
        Next
    Next
End Function

edited Jun 26, 2022 at 6:50

answered Jun 25, 2022 at 11:04

Mik

2,1011 gold badge7 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

simsim Over a year ago

Unfortently, it didn't work. I would like to get the Item "CHINA" in the text " Move the dev from China to Newseeland" disregarding the rest of Word in CHINA-Country, like that, here the example

simsim Over a year ago

Sub test() MsgBox IsInStr_IgnoreCase("CHINA-Country", "Move the dev from China to Newseeland") End Sub Function IsInStr_IgnoreCase(ByVal Str As String, ByVal Value As String) As Boolean Dim objRegEx As Object Set objRegEx = CreateObject("VBScript.RegExp") objRegEx.IgnoreCase = True objRegEx.Pattern = Value IsInStr_IgnoreCase = objRegEx.test(Str) ' objRegEx.test(Str) returns True if Sustr exists. 'Me.objRegExp.IgnoreCase = False End Function

Mik Over a year ago

HI simsim, I added new solutions, so you can test them.

Collectives™ on Stack Overflow

find a complex string in a substring in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related