none
Trouble searching string for substring RRS feed

  • Question

  • Thanks for taking the time to read my question.

    I need to search a very long string for the first occurrence of a substring. My string contains asterisks (*) in it so I've added the [] to my substring.

    here is what I'm trying with no success.

    sTemp has 'ST*864*' in it which I want InStr to find.

    Temp = "ST[*]###[*]"
            Debug.Print Temp
            
            If Not InStr(1, sTemp, "ST*850*", vbTextCompare) Then
                Debug.Print InStr(1, sTemp, Temp, vbTextCompare)
                Name defaultpath & NewName As defaultpath & NewName & InStr(1, sTemp, "ST[*]???[*]", vbTextCompare)
            End If

    when I get to the Debug.Print InStr... line I keep getting 0 which means it's not found.

    if I make Temp = "ST*864*" then it is found at 174.

    I have many 3 digit numbers that can replace 864 and don't want to check for them all individually.

    Any help would be great.

    Thanks,
    Brad

     
    Monday, July 20, 2015 9:22 PM

Answers

  • Hi Brad,

    I suggest you run a loop. Start by looking for the ST* when found test if the next 3 characters are numeric then finally test if the 7th is *

    Something like this...

    iStart = 1
    Do
      iMatch = instr(iStart,strTest,"ST*",vbDatabaseCompare) 
      if iMatch > iStart  then
         if isnumeric(mid(strTest,iMatch +1,3) then
            if mid(strTest(iMatch+5,1) = "*" then
               Debug.Print "Found: " & mid(strTest,iMatch+1,7) _
               & " At: " & iMatch
               exit do
            end if
            iStart = iMatch + 1
          end if
    
      Else 'Not Found
        exit do
      End if
    Loop
    
    
    
    
    


    Brian, ProcessIT- Hawke's Bay, New Zealand

    • Marked as answer by mbrad Monday, July 20, 2015 10:14 PM
    Monday, July 20, 2015 10:00 PM

All replies

  • InStr does not utilize pattern match searching.  To do that, you will want to use RegExp.  You will need to include a reference for Microsoft VBScript Regular Expressions 5.5.  You can search Google or MSDN for information regarding it's usage.
    Monday, July 20, 2015 9:39 PM
  • if I make Temp = "ST*864*" then it is found at 174.

    I have many 3 digit numbers that can replace 864 and don't want to check for them all individually.

    Hi Brad,

    You can search for the first occurrence of "ST*", and accept it as a valid position if the character on found_position + 6 is "*".

    Imb.

    Monday, July 20, 2015 9:46 PM
  • Hi Brad,

    I suggest you run a loop. Start by looking for the ST* when found test if the next 3 characters are numeric then finally test if the 7th is *

    Something like this...

    iStart = 1
    Do
      iMatch = instr(iStart,strTest,"ST*",vbDatabaseCompare) 
      if iMatch > iStart  then
         if isnumeric(mid(strTest,iMatch +1,3) then
            if mid(strTest(iMatch+5,1) = "*" then
               Debug.Print "Found: " & mid(strTest,iMatch+1,7) _
               & " At: " & iMatch
               exit do
            end if
            iStart = iMatch + 1
          end if
    
      Else 'Not Found
        exit do
      End if
    Loop
    
    
    
    
    


    Brian, ProcessIT- Hawke's Bay, New Zealand

    • Marked as answer by mbrad Monday, July 20, 2015 10:14 PM
    Monday, July 20, 2015 10:00 PM
  • Monday, July 20, 2015 10:13 PM
  • This code worked great! Thank you so much for your post Brian.

    Have a great...day? Your signature says New Zealand.

    Brad

    Monday, July 20, 2015 10:14 PM
  • If you want to use pattern matching, you can try the procedure below.  Again you have to include a reference for Regular Expressions as mentioned above.

    Function ReEx() Dim objRE As RegExp Dim objMatch As Match Dim objMatches As MatchCollection Dim retStr As String Dim lngCount As Long
    Dim strTest As String

    strTest = "There are 3 matches for ST*123* and ST*456* and ST*789*" Set objRE = New RegExp objRE.Pattern = "ST\*\d{3}\*" objRE.Global = True Set objMatches = objRE.Execute(strTest) For Each objMatch In objMatches Debug.Print objMatch.Value Next objMatch Set objRE = Nothing Set objMatches = Nothing End Function



    Monday, July 20, 2015 10:23 PM
  • You are welcome.

    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Monday, July 20, 2015 10:47 PM
  • This code worked great! Thank you so much for your post Brian.

    Have a great...day? Your signature says New Zealand.

    Brad

    Im not sure how Brian's code worked for you unless you corrected some of the flaws in the procedure.  The major issue with the code is that it will result in an endless loop if a match isn't found.  The numeric calculations for string lengths are off as well.  There are some syntax errors that need correcting too.  I've corrected the issues and the new code is available below:

    Function InStrSTNum()
        
        Dim lngStart As Long
        Dim strTest As String
        Dim lngMatch As Long
        
        strTest = "There are 3 matches for ST*123* and ST*456* and ST*789*"
        
        lngStart = 1
        Do
          lngMatch = InStr(lngStart, strTest, "ST*", vbBinaryCompare)
          If (lngMatch > 0) Then
             If (IsNumeric(Mid(strTest, lngMatch + 3, 3))) Then
                If (Mid(strTest, lngMatch + 6, 1) = "*") Then
                   Debug.Print "Found: " & Mid(strTest, lngMatch, 7) & " At: " & lngMatch
                   'Move forward to end of match - 1
                   lngStart = lngMatch + 7 - 1
    End If End If End If 'Move forward regardless if match found or not to avoid endless loop lngStart = lngStart + 1 Loop While lngMatch > 0 End Function





    Monday, July 20, 2015 11:08 PM