CODEWITHSUNDEEP
regexexcelvba
Raphael Pires
Raphael Pires

Reputation: 43

How To Combine Multiple RegExp Patterns - Excel VBA

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: enter image description here

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

Upvotes: 1

Views: 1242

Answers (2)

Raphael Pires
Raphael Pires

Reputation: 43

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:

enter image description here

Upvotes: 0

Marcucciboy2
Marcucciboy2

Reputation: 3257

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

Upvotes: 1

Related Questions