Extract a Sub String using regEx

[actjfc]

Excel Friends,

I developed this code and it works fine, but I need that the actual string found be the result of the function. For example:

If the String is fsdhjfh383hjdjhf8ur2hjfhds/O123456/nfejhfjhef8343847832832/dhhfjshjfh/dhsjdjd the function result should be: O123456

Thanks for any help!

Function STRNEEDED(ByVal StrTested As String) As Boolean
'This function checks the syntax of the StrTesteds
Dim regEx As New VBScript_RegExp_55.RegExp
    With regEx
        .ignorecase = True
        .Pattern = "\\[a-zA-Z]{1}[0-9]{6}\\"
        If .test(StrTested) = False Then GoTo BadStrTested
    End With
    STRNEEDED = True
 
    'String Found = ???????
  
    Exit Function
    BadStrTested:
    STRNEEDED = False
  
    'String Found = "N/A"

End Function

 

[Scott Huish]

Re: How to extract a Sub String using regEx

If it's always the 2nd item delimited by / there is a much easier way.

However, perhaps:

Function StrFound(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "[a-zA-Z][0-9]{6}"
    If .test(r) Then StrFound = .Execute(r)(0) Else StrFound = "NOT FOUND"
End With
End Function

 

[JoeMo]

Re: How to extract a Sub String using regEx

I don't think your pattern ("\\[a-zA-Z]{1}[0-9]{6}") is correct. Scott's function will extract your desired result provided it appears first in your string. The function below includes the delimiters "/" to make it more robust and will allow for more than one sub-string that meets the pattern.

Function STRFOUND(StrTested As String) As String
Dim S
    With CreateObject("VBScript.regexp")
        .ignorecase = True
        .Pattern = "\/[a-zA-Z]{1}[0-9]{6}\/"
        If .test(StrTested) Then
            For Each Match In .Execute(StrTested)
                S = S & " " & Match
            Next Match
        End If
    End With
    STRFOUND = Replace(S, "/", "")
End Function

 

[Dim Me as xlNoob]

Re: How to extract a Sub String using regEx

In order to match multiple substrings within the target string you must set .Global = True for the RegEx object.

Just another angle on this if you want to be able to specify the nth match in the string:

Function STRFOUND(StrTested As String, Optional nth As Integer = 1) As String
    With CreateObject("VBScript.regexp")
        .Global = True
        .Pattern = "(?!/)[a-zA-Z][0-9]{6}(?=/)"
        STRFOUND = .Execute(StrTested)(nth - 1)
    End With
End Function

Also included in the pattern is the forward slash delimeters, but they are not included in the execution by using forward look and negative forward look assertion.
You could then implement this on your worksheet with the target string in A1 like this:

B2 =IFERROR(StrFound($A2,COLUMNS($B2:B2)),"")

Then drag accross for each consecutive match, and drag down for each string you want to search.

[TABLE="class: grid, width: 797, align: center"]
<tbody>[TR]
[TD]TEST STRING[/TD]
[TD]MATCH 1[/TD]
[TD]MATCH 2[/TD]
[TD]MATCH 3[/TD]
[/TR]
[TR]
[TD]fsdhjfh383hjdjhf8ur2hjfhds/O123456/nfejhfjhe/f834384/h783283/2/dhhfjshjfh/dhsjdjd[/TD]
[TD]O123456[/TD]
[TD]f834384[/TD]
[TD]h783283[/TD]
[/TR]
</tbody>[/TABLE]

 

[actjfc]

Re: How to extract a Sub String using regEx

My strings could have many /Random/ patterns, but only one will be like /O123456/. I cannot make any of this subs work. Do I need to turn on a reference Library? Which one? I tested VBScript Regular Expressions 5.5, and it does not work for me. Thanks for your help!

 

[Scott Huish]

Re: How to extract a Sub String using regEx

All of our codes use late binding so should work as is. I've included a macro to run as a test, what do you get when you run the Macro Test below?

Function StrFound(r As String) As String
With CreateObject("vbscript.regexp")
    .Pattern = "\/[a-zA-Z][0-9]{6}\/"
    If .Test(r) Then StrFound = .Execute(r)(0) Else StrFound = "NOT FOUND"
End With
End Function

Sub Test()
MsgBox StrFound("fsdhjfh383hjdjhf8ur2hjfhds/O123456/nfejhfjhef8343847832832/dhhfjshjfh/dhsjdjd")
End Sub

 

[actjfc]

Re: How to extract a Sub String using regEx

Interesting the test works with the string above, but it does not work with this one:
"\\comp.xyz.com\SpecialFolders\XYZCO\LA_Home\O123456\Documents\TEST\FolderA"

So the problem is in the Pattern. Thanks for your help!

 

[Rick Rothstein]

Re: How to extract a Sub String using regEx

Interesting the test works with the string above, but it does not work with this one:
"\\comp.xyz.com\SpecialFolders\XYZCO\LA_Home\O123456\Documents\TEST\FolderA"

So the problem is in the Pattern. Thanks for your help!

One reason it might not work is because your first text used forward slashes and the text above is using backward slashes. Which one, or is it both, that your text contains?

 

[actjfc]

Re: How to extract a Sub String using regEx

Yes! THANKS! This is the type of errors that one can spend hours and do not see it! Yes, you are right I changed the pattern to Pattern = "\\[a-zA-Z][0-9]{6}" and it works perfect!

Thanks to all posts! You are all great!