Excel Regex VBA

I need to extract a part of string from an excel cell if it matches the following format:
"DDD/L-DDD-L/DDD-L-DD/DD" where D represents any digit and L represents any letter, upper or lower case.

Here are some examples: <br />
Cell content: "BHL: 200/b-003-A/094-G-08/02" <br />
Extracted string: "200/b-003-A/094-G-08/02" <br />

Cell content: "UWI: 200/c-098-C/094-G-01/00" <br />
Extracted string: "200/c-098-C/094-G-01/00" <br />

Cell content: "TOTAL SULPHUR (mass%) = 0.013, BHL: 203/a-044-C/094-G-01/00" <br />
Extracted string: "203/a-044-C/094-G-01/00" <br />

NOTE: The data formatting is very inconsistent so looking for keywords like "BHL" or "UWI" is not option. <br />

I've made the following VBA function but its not working as expected

Function simpleCellRegex(Myrange As Range) As String
    Dim regEx As New RegExp
    Dim strPattern As String
    Dim strInput As String
    Dim strReplace As String
    Dim strOutput As String
    
    
    strPattern = "[0-9]{3}/[A-Za-z]-[0-9]{3}-[A-Za-z]/[0-9]{3}-[A-Za-z]-[0-9]{2}/[0-9]{2}"
    
    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""
        
        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .pattern = strPattern
        End With
        
        If regEx.Test(strInput) Then
            simpleCellRegex = regEx.Replace(strInput, strReplace)
        Else
            simpleCellRegex = "Not matched"
        End If
    End If
End Function

Just to give you a non-regex/VBA answer; what are the odds that any other part of the cell's content would follow this pattern of forward slashes and hyphens? In other words, would the following work?

Formula in B1:

=MID(A1:A3,SEARCH("???/?-???-?/???-?-??/??",A1:A3),23)

If that type of security isn't enough, you can still do this without regular expressions or VBA:

=LET(x,MID(A1,SEQUENCE(LEN(A1)),23),FILTER(x,MAP(x,LAMBDA(y,LET(z,MID(y,{1,5,7,11,13,17,19,22},{3,1,3,1,3,1,2,2}),AND(AND(ISNUMBER(--CHOOSECOLS(z,{1,3,5,7,8}))),AND(ISNUMBER(SEARCH(CHOOSECOLS(z,{2,4,6}),"abcdefghijklmnopqrstuvwxyz"))),ISNUMBER(SEARCH("???/?-???-?/???-?-??/??",y)))))),"No Matches"))

The breakdown in short:

• MID(A1,SEQUENCE(LEN(A1)),23) - Get any possible substring in from the input of 23 characters long;

• MID(y,{1,5,7,11,13,17,19,22},{3,1,3,1,3,1,2,2}) - Create an array of subsequent substring. This will grab all alphanumeric parts;

• AND(ISNUMBER(--CHOOSECOLS(z,{1,3,5,7,8}))) - Test that the listed columns are all numeric;

• AND(ISNUMBER(SEARCH(CHOOSECOLS(z,{2,4,6}),"abcdefghijklmnopqrstuvwxyz"))) - Test that all listed columns are found in the alphabet (case insensitive);

• ISNUMBER(SEARCH("???/?-???-?/???-?-??/??",y)) - Test for all forward slashes and hyphens to be in the right place;

• The FILTER() will now use the outer AND() to return all possible matches (not just one).

You should use regExp.Execute(strInput) (not Replace) and look at the returned Matches collection

This works for me:

Function simpleCellRegex(v) As String
    Const strPattern = "\d{3}/[A-Z]-\d{3}-[A-Z]/\d{3}-[A-Z]-\d{2}/\d{2}"
    
    Dim regEx As New RegExp, matches As Object, match As Object, sep As String
    
    With regEx
        .Global = True
        .MultiLine = True
        .IgnoreCase = True
        .Pattern = strPattern
    End With
    
    Set matches = regEx.Execute(v)
    If matches.Count > 0 Then
        For Each match In matches
            simpleCellRegex = simpleCellRegex & sep & match.Value
            sep = "; "
        Next match
    Else
        simpleCellRegex = "Not matched"
    End If
End Function

Here is a reference.

You can match the pattern you provided, given the following.

The (?i) toggles case-insensitive mode, on.
And, \d matches any digit, 0 through 9.

(?i)\d{3}/[a-z]-\d{3}-[a-z]/\d{3}-[a-z]-\d\d/\d\d

Output

200/b-003-A/094-G-08/02
200/c-098-C/094-G-01/00
203/a-044-C/094-G-01/00