VBA - Substring position using String Masking

[GlennL]

Hello,

Is there a simple way of finding a position of a substring within a string using a string-mask?

This is the original substring
06.12.2022 04:31:39 UTC

and I want to search for it using the mask
++.++.++++ ++:++:++ UTC

I could make a function which loops through the string and replaces the integers with the +, however, I thought there maybe an easier way. Is there?

 

[Legacy 143009]

Like this?

If str Like "*??.??.???? ??:??:?? UTC*" Then
  'Do stuff
End If

 

[GlennL]

I also need to know where it is in the string

 

[Legacy 143009]

Something like this maybe:

Sub Test()
  Dim str() As String
  Dim testStr As String
  
  testStr = Cells(1, 1).Value
  str = Split(testStr)
    For i = 0 To UBound(str) - 2
    For j = i + 1 To UBound(str) - 1
      For k = i + 1 To UBound(str)
        If str(i) & " " & str(j) & " " & str(k) Like "*??.??.???? ??:??:?? UTC*" Then
          MsgBox InStr(testStr, str(i) & " " & str(j) & " " & str(k))
        End If
      Next k
    Next j
  Next i
End Sub

 

[Legacy 143009]

A slightly efficient version:

Sub Test()
  Dim str() As String
  Dim testStr As String
 
  testStr = Cells(1, 1).Value
  str = Split(testStr)
  For i = 0 To UBound(str) - 2
    If str(i) & " " & str(i + 1) & " " & str(i + 2) Like "*??.??.???? ??:??:?? UTC*" Then
      MsgBox InStr(testStr, str(i) & " " & str(i + 1) & " " & str(i + 2))
    End If
  Next
End Sub

 

[JvdV]

You could also get the starting position of a regex-match;

Sub Test()

Dim s As String: s = "Test 06.12.2022 04:31:39 UTC Test"
Debug.Print RegexMatchPos(s, "\d\d\.\d\d\.\d{4} \d\d:\d\d:\d\d UTC")

End Sub

Function RegexMatchPos(s_in As String, pat As String) As Long

With CreateObject("vbscript.regexp")
    .Pattern = pat
    If .Test(s_in) Then
        RegexMatchPos = .Execute(s_in)(0).FirstIndex + 1
    Else
        RegexMatchPos = 0
    End If

End With

End Function

 

[JvdV]

Another option could be to apply worksheet functionality:

Sub Test()

Dim s As String: s = "Test 06.12.2022 04:31:39 UTC Test"
Debug.Print Application.Search("??.??.???? ??:??:?? UTC", s)

End Sub

 

[JEC]

VBA

Sub jec()
 Dim xStr
 xStr = "Test 06.12.2022 04:31:39 UTC Test"
 
 With CreateObject("vbscript.regexp")
   .Pattern = "\d+\..*\d+:.*UTC"
   If .Test(xStr) Then Debug.Print .Execute(xStr)(0).firstindex + 1
 End With
End Sub

Formula

=SEARCH("??.*UTC",A1)