How To Combine Multiple RegExp Patterns - Excel VBA

Question

I have two functions to extract an exact sequence of characters, but I can not join the functions.

Would it be possible to put them together?

I tried some ways, but it always returns me error.

Public Function ExtraiNúmCNJ(Texto As Variant) As String
Application.Volatile True
With CreateObject("VBScript.Regexp")
 .Global = False
 .Pattern = "\d{7}\s*\.?\s*\d{2}\s*\.?\s*\d{4}\s*\.?\s*\d\s*\.?\s*\d{2}\s*\.?\s*\d{4}"
 If .test(Texto) Then
   ExtraiNúmCNJ = .Execute(Texto)(0)
   .Global = True: 
   .Pattern = "\D"
   ExtraiNúmCNJ = Format(.Replace(ExtraiNúmCNJ, ""), "0000000\-00\.0000\.0\.00\.0000")
  End If
 End With
End Function

Public Function ExtraiNúmAntigo(Texto As Variant) As String
Application.Volatile True
With CreateObject("VBScript.Regexp")
 .Global = False
 .Pattern = "\d{4}\s*\.?\s*\d{2}\s*\.?\s*\d{6}\s*\.?\s*\d{1}"
 If .test(Texto) Then
   ExtraiNúmAntigo = .Execute(Texto)(0)
   .Global = True: 
   .Pattern = "\D"
   ExtraiNúmAntigo = Format(.Replace(ExtraiNúmAntigo, ""), "0000\.00\.000000\.0")
  End If
 End With
End Function

I currently use it as follows:

I'm trying to leave with only one function by pulling the two types of numbers.

@Marcucciboy2 My functions work, I wanted to put the two together. — Raphael Pires, Aug 27 '18 at 17:43
Those are beasts of regex functions. You should be able to just toss an pipe in the middle to act as an "Or" *probably* `\d{7}\s*\.?\s*\d{2}\s*\.?\s*\d{4}\s*\.?\s*\d\s*\.?\s*\d{2}\s*\.?\s*\d{4}|\d{4}\s*\.?\s*\d{2}\s*\.?\s*\d{6}\s*\.?\s*\d{1}` — JNevill, Aug 27 '18 at 17:54
I used "|" to separate the .Pattern, but I need the two results, one with 20 characters and the other with 13 characters. — Raphael Pires, Aug 27 '18 at 18:00

score 1 · Answer 1 · answered Aug 28 '18 at 16:42

This should do the trick.

Public Function ExtraiNúm(Texto As Variant) As String

    Application.Volatile True


    Dim regexCNJ As Object
    Set regexCNJ = CreateObject("vbscript.regexp")

    With regexCNJ
        .Global = False
        .MultiLine = True
        .IgnoreCase = False
        .pattern = "\d{7}\s*\-?\s*\d{2}\s*\.?\s*\d{4}\s*\.?\s*\d\s*\.?\s*\d{2}\s*\.?\s*\d{4}|\d{7}\s*\.?\s*\d{4}\s*\.?\s*\d\s*\.?\s*\d{2}\s*\.?\s*\d{4}"
    End With


    Dim regexAntigo As Object
    Set regexAntigo = CreateObject("vbscript.regexp")

    With regexAntigo
        .Global = False
        .MultiLine = True
        .IgnoreCase = False
        .pattern = "\d{4}\s*\.?\s*\d{2}\s*\.?\s*\d{6}\s*\.?\s*\d{1}"
    End With


    Select Case True
        Case regexCNJ.test(Texto)
            Texto = regexCNJ.Execute(Texto)(0)

        Case regexAntigo.test(Texto)
            Texto = regexAntigo.Execute(Texto)(0)

    End Select


    ExtraiNúm = Texto

End Function

Your code returned all the contents of the cell, but I ended up understanding how to join the functions, thank you anyway. — Raphael Pires, Aug 28 '18 at 19:11

score 0 · Accepted Answer · answered Aug 28 '18 at 19:07

Following the tips of @Marcucciboy2 and @JNevill, I ended up getting the result I was expecting.

I added "|" in .Pattern and on line 10 I used "ExtraiNumProc = Trim(ExtraiNumProc)".

Public Function ExtraiNumProc(Texto As Variant) As String
 Application.Volatile True
  With CreateObject("VBScript.Regexp")
   .Global = False
   .Pattern = "\d{7}\s*\.?\s*\d{2}\s*\.?\s*\d{4}\s*\.?\s*\d\s*\.?\s*\d{2}\s*\.?\s*\d{4}|\d{7}\s*\-?\s*\d{2}\s*\.?\s*\d{4}\s*\.?\s*\d\s*\.?\s*\d{2}\s*\.?\s*\d{4}|\d{4}\s*\.?\s*\d{2}\s*\.?\s*\d{6}\s*\.?\s*\d{1}|\d{4}\s*\.?\s*\d{2}\s*\.?\s*\d{6}\s*\-?\s*\d{1}"
 If .test(Texto) Then
   ExtraiNumProc = .Execute(Texto)(0)
   .Global = True:
   .Pattern = "\D"
   ExtraiNumProc = Trim(ExtraiNumProc)
  End If
 End With
End Function

The result was this:

How To Combine Multiple RegExp Patterns - Excel VBA

2 Answers2