UDF regular expression question

[mcranmoss]

The left column in the data below is a list of filenames. I have created a UDF which extract strings which are 'product codes'.

Extracting product codes involves extracting everything except:

RP1
RP2
RP3
RP4
_ underscores
ddmmyy or ddmmyyyy or dd.mm.yy (dates)
.txt


I could only make this work in my regular expression by changing all of the above to spaces, and trimming the result.

Basically, this seemed a bit clumsy, and, for the sake of feeling more confident using regular expressions, I would like to construct it to suppress the above strings.



UY3_RP2_26.07.11.txt - UY3
UY3_RP3_26.07.11.txt - UY3
V5_RP3_090811.txt - V5
VN_RP3_21.07.11.txt - VN
VV2_RP3_09.08.11.txt - VV2
VW2_RP3_03.08.11.txt - VW2
02_RP2_B7V_270211.txt - 02 B7V
02_RP2_B7X_270211.txt - 02 B7X
02_RP3_B4X_270211.txt - 02 B4X
02_RP3_B7T_270211.txt - 02 B7T
02_RP3_B7V_270211.txt - 02 B7V



=TRIM(ExtractProductCode(A1))

Function ExtractProductCode(ByVal s As String) As String
Dim rx As Object
 
Set rx = CreateObject("VBScript.RegExp")
With rx
    .Pattern = "(RP1|RP2|RP3|RP4|\d{6,8}|_|.txt|\d{2}\.\d{2}\.\d{2})"
    .Global = True
    ExtractProductCode = .Replace(s, " ")
End With
End Function

 

[pgc01]

Hi

I tried this one on your examples and it worked.

Try:

Function ExtractProductCode(ByVal s As String) As String
Dim rx As Object
 
Set rx = CreateObject("VBScript.RegExp")
With rx
    .Pattern = "^(.*?)_?(?:RP[1234])_?(.*?)_?(\d\d\.\d\d\.\d\d|\d{6,8})\.txt$"
    .Global = True
    ExtractProductCode = Trim(.Replace(s, "$1 $2"))
End With
End Function

 

[mcranmoss]

Wonderful!

Thanks. This has given me some new techiques to brush up on.

mark

 

[pgc01]

I'm glad it helped. Thanks for the feedback.

Remark:

The statement:

    .Global = True

is not necessary. I had copied it from your code and forgot to delete it.