Cleaning string using VBA - finding text position in string with wildcards

[Skiff]

Hi,
I'm looking for a way to clean string in VBA.

What I want to do:
1) search if string contains number in format 3123456/001 where numbers after "3" are random, and 8th character is "/" - in wildcard this looks like that 3######/0## (already done and working)
2) if string contains desired number to remove from string anything but this number

So I am stuck at cleaning string. I've wanted to use InStr, but since it disallow using wildcards it is no use for me.
I want to find string position to trim number and delete another data from string.

I've searched the web and come up with this, but it doesn't work, and does not produce any error

My full code:

Sub temat()
   Dim MainString As String, i As Integer, DaneTematu As String

'check if string contains number - already working
   DaneTematu = Trim(Range("A2").Value)
   If DaneTematu Like "*3######/0##*" = False Then MsgBox "Temat nie zawiera numeru szkody"
   If DaneTematu Like "*3######/0##*" = True Then MsgBox "Temat zawiera numer szkody"

 'this doesn't work
   For i = 1 To Len(DaneTematu)
   If Mid(DaneTematu, i, 10) Like "*3######/0##*" Then MsgBox i
   'End If
   Next
   
   'Trim("String") - to use later
End Sub

Code to solve for finding text position in string:

Sub temat()
   Dim MainString As String, i As Integer, DaneTematu As String

   DaneTematu = Trim(Range("A2").Value)

   For i = 1 To Len(DaneTematu)
   If Mid(DaneTematu, i, 10) Like "*3######/0##*" Then MsgBox i
   'End If
   Next

 

[Skiff]

Never mind. I just noticed, that my number have 11 characters, and not 10 so the solution was

For i = 1 To Len(DaneTematu)
If Mid(DaneTematu, i, 11) Like "*3######/0##*" Then MsgBox i

 

[Skiff]

If anybody is looking for similar solution then I supply the code

Sub clean_string()
   Dim MainString As String, CleanedString As String, strpos As Integer
   
   MainString = Trim(Range("A2").Value)
   If MainString Like "*3######/0##*" = False Then MsgBox "There is no searched string"
   
   If MainString Like "*3######/0##*" = True Then
      For strpos = 1 To Len(MainString)
         If Mid(MainString, strpos, 11) Like "*3######/0##*" Then
            MsgBox "Position: " & strpos
            Exit For
         End If
      Next
   End If
   
   CleanedString = Mid(MainString, strpos, 11)
   
   MsgBox "Cleaned string: " & CleanedString
End Sub

 

[Peter_SSs]

Welcome to the MrExcel board!

Here is a slight variation also. Note ..
a) The use of If..Then..Else..End If rather than 2 completely separate If blocks. Also no need for the True/False comparisons.
b) There is no need for the asterisks in your second use of the Like operator as you have already established your pattern of interest exists and you know it is exactly 11 characters long.
c) If the sought after pattern is not found, your existing code errors on the line CleanedString = Mid(MainString, strpos, 11) because strpos equals zero at that point.
d) vba converts Integer data types to Long type before use so you might as well declare them as Long to start with.

Sub clean_string_2()
  Dim MainString As String, CleanedString As String, strpos As Long
  
  MainString = Trim(Range("A2").Value)
  If MainString Like "*3######/0##*" Then
    strpos = 1
    Do Until Mid(MainString, strpos, 11) Like "3######/0##"
      strpos = strpos + 1
    Loop
    CleanedString = Mid(MainString, strpos, 11)
    MsgBox "Position: " & strpos & vbLf & "Cleaned string: " & CleanedString
  Else
    MsgBox "There is no searched string"
  End If
End Sub

 

[Peter_SSs]

'Regular Expressions' can also often be used when searching for patterns withing a string. So the problem could also be solved like this.

Sub clean_string_3()
  Dim MainString As String, CleanedString As String, strpos As Long
  
  MainString = Trim(Range("A2").Value)
  With CreateObject("VBScript.RegExp")
    .Pattern = "3\d{6}/0\d\d"
    If .Test(MainString) Then
      CleanedString = .Execute(MainString)(0)
      strpos = .Execute(MainString)(0).FirstIndex + 1
      MsgBox "Position: " & strpos & vbLf & "Cleaned string: " & CleanedString
    Else
      MsgBox "There is no searched string"
    End If
  End With
End Sub

 

[Skiff]

Thanks for replies. In mine limited knowledge into programming your first solution is much more understandable (I'm totally self-educated on examples from web and macro recording for correct syntax).

I'll try to incorporate this to my code.

You're right that second Like expression doesn't need asterisks since string is always 11 characters long. Interesting is that I have not stumble on any errors during extensive testing of code as you mentioned in point (c).

Of course my code have message boxes only for testing values purposes (except the last one), and in final code that I use for Outlook is without them (sadly lack of macro recorder for Outlook makes this much more complicated).

Regards
Skiff

 

[Peter_SSs]

Interesting is that I have not stumble on any errors during extensive testing of code as you mentioned in point (c).

If cell A2 contains, say, "abc" and I run the code from post #3, I get a message box "There is no searched string" & when I click 'OK' to that I get a "Run-time error '5': Invalid procedure call or argument". Are you saying that does not happen for you?

 

[Skiff]

Yes, I didn't have that, but my testing was in Outlook, not in Excel. Either way I'm just checking your code and putting it into my Outlook code. Big thanks for help!

 

[Peter_SSs]

No problem. Glad to help.