Thread: Trying to learn how to use CreateObject("VBScript.RegExp")

Team,

I am trying to learn how to create code and patterns for CreateObject("VBScript.RegExp").

With the following information in column A, beginning in cell A1:
GRP10-0137-
GRP10-0212-PO10-0213
20032010 - MAJU TERUS-PO10-0088-GRP10-0071
20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071


Using CreateObject("VBScript.RegExp"), what would the Function and Pattern be to extract the bold PO infomation into column B, or return a 0 (zero)?

I'm not very good at RegEx I'm afraid but whilst the others come up with something perhaps the below might help get the cogs turning...

.Pattern = "PO\d{2}-\d{4}(-[A-Z]-){0,1}"

above assumes you're looking for "PO##-####-?" where -? is variable (and ? is 1 char in length [if exists])

Assuming you don't want the closing hyphen (in case of -A-) you might want to check the matching output and adjust accordingly (ie remove if last char is hyphen)

EDIT:

I failed to notice you were looking for more than just the pattern....

Function PatternExtract(rngString As Range, strPattern As String, _
            Optional boolIgnoreCase As Boolean = True, Optional lngInstance As Long = 1) As Variant
    Dim RegExp As Object, RegExpMatch As Object
    On Error Resume Next
    Set RegExp = CreateObject("vbscript.regexp")
    With RegExp
        .Global = True
        .IgnoreCase = boolIgnoreCase
        .Pattern = strPattern
    End With
    Set RegExpMatch = RegExp.Execute(rngString)
    If lngInstance > RegExpMatch.Count Then
        PatternExtract = ""
    Else
        PatternExtract = RegExpMatch(lngInstance - 1)
    End If
    Set RegExpMatch = Nothing
    Set RegExp = Nothing
End Function

The above is rather generic (ie not specific to this thread) - ie pass the pattern to the UDF rather than storing the pattern within the UDF.

=PATTERNEXTRACT(A1,"PO\d{2}-\d{4}(-[A-Z]-){0,1}")

of course the pattern need not be a constant (ie reference to another cell) and you have additional optional parameters to extract particular instances / affect Case sensitivity etc...

Hi stanleydgromjr
This is the same as DO's but with a different error handler

Option Explicit
Function PatternExtractTwo(rngString As Range)
Dim oRgx As Object
Set oRgx = CreateObject("VBScript.RegExp")
With oRgx
    .IgnoreCase = True
    .Global = True
    .Pattern = "PO\d{2}-\d{4}(-[A-Z]-){0,1}"
       If .test(rngString) = True Then
       PatternExtractTwo = .Execute(rngString)(0)
        Else
        PatternExtractTwo = "no"
        End If
End With
End Function

Originally Posted by pike

Hi stanleydgromjr
This is the same as DO's but with a different error handler

Not quite the same ... there are a few other variables in the first Function (hence the additional lines of code)

The handler in the first accounts for possibility that there may be a match but that the specific match being returned exceeds the number of matches found.
(ie interested in third match but only two matches found)

true ,
just wanted to post the pattern . it did take me a while to write
Its near imposible to stump the DO

Originally Posted by pike

Its near imposible to stump the DO

far from it unfortunately... ... I also suspect there's a much better pattern.

DonkeyOte,

With a slight modification it works.

Thank you so very much.

What a great site for learning.

Function PatternExtract(rngString As Range, strPattern As String, _
            Optional boolIgnoreCase As Boolean = True, Optional lngInstance As Long = 1) As Variant
'
' Original function created by DonkeyOte, 09/03/2010, EF744451
' Modified by stanleydgromjr, 09/04/2010
'
'=PATTERNEXTRACT(A1,"PO\d{2}-\d{4}(-[A-Z]-){0,1}",1)
'
'If A1 = "20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071"
'Then B1 = "PO10-0087-A"
'
Dim RegExp As Object, RegExpMatch As Object
On Error Resume Next
Set RegExp = CreateObject("vbscript.regexp")
With RegExp
  .Global = True
  .IgnoreCase = boolIgnoreCase
  .Pattern = strPattern
End With
Set RegExpMatch = RegExp.Execute(rngString)
If lngInstance > RegExpMatch.Count Then
  PatternExtract = 0
Else
  If Right(RegExpMatch(lngInstance - 1), 1) = "-" Then
    PatternExtract = Left(RegExpMatch(lngInstance - 1), Len(RegExpMatch(lngInstance - 1)) - 1)
  Else
    PatternExtract = RegExpMatch(lngInstance - 1)
  End If
End If
Set RegExpMatch = Nothing
Set RegExp = Nothing
End Function

Can you have in a Pattern that would exclude, or stop at, some string characters like -GRP?

Originally Posted by sd

an you have in a Pattern that would exclude, or stop at, some string characters like -GRP?

Undoubtedly but could you clarify requirements by means of example(s) ?

For info. on Reg Exp: http://www.regular-expressions.info/

To reiterate I'm no expert and muddle my way through - plenty here are however.

for info. this thread has extensions elsewhere which may be of interest
(at least to those who of us who like to see clever pattern constructs - c/o Pedro at MrE)

http://www.mrexcel.com/forum/showthread.php?t=492837

pgc01, DonkeyOte,

Sorry for not cross posting. I was trying to find a solution for penambangliar in an area that I am trying to learn about, VBScript.RegExp.

MrExcel and Excel Forum are by far the two best sites I have come accross where I have learned so much to improve my VBA skills.

Thank you, thank you so much.

pgc01, DonkeyOte,

For the data posted in my request, here is my latest solution, per the help of pgc01, and DonkeyOte.

Option Explicit
Sub GetMyPO()
' hiker95, 09/04/2010, ME492837
Dim c As Range
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  c.Offset(, 1) = GetPO(c.Value)
Next c
Columns(2).AutoFit
Application.ScreenUpdating = True
End Sub


Function GetPO(s As String)
'pgc01
'http://www.mrexcel.com/forum/showthread.php?t=492837
'
'If A1 = "20032010 - MAJU MUNDUR-PO10-0087-A-GRP10-0071"
'B1 = GetPo(A1)
'Then B1 = "PO10-0087-A"
'
With CreateObject("VBScript.RegExp")
    .Pattern = "PO.*(?=-GRP)|PO.*"
    If .test(s) Then GetPO = .Execute(s)(0) Else GetPO = 0
End With
End Function