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

Skiff

Board Regular
Joined
May 30, 2016
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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:
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:
Code:
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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Skiff

Board Regular
Joined
May 30, 2016
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
Never mind. I just noticed, that my number have 11 characters, and not 10 so the solution was

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

Skiff

Board Regular
Joined
May 30, 2016
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
If anybody is looking for similar solution then I supply the code

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
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.

Rich (BB code):
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
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Rich (BB code):
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

Board Regular
Joined
May 30, 2016
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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? :confused:
 

Skiff

Board Regular
Joined
May 30, 2016
Messages
53
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,007
Office Version
  1. 365
Platform
  1. Windows
No problem. Glad to help.
 

Forum statistics

Threads
1,143,637
Messages
5,719,962
Members
422,251
Latest member
bonebreaker100

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top