title: Regular Expressions in Excel VBA
body: <p>I'm using the Microsoft regular expression engine in Excel VBA. I'm very new to regex but I have a pattern working right now. I need to expand it and I'm having trouble. Here is my code so far:</p>
  
  <pre><code>Sub ImportFromDTD()
  
  Dim sDTDFile As Variant
  Dim ffile As Long
  Dim sLines() As String
  Dim i As Long
  Dim Reg1 As RegExp
  Dim M1 As MatchCollection
  Dim M As Match
  Dim myRange As Range
  
  Set Reg1 = New RegExp
  
  ffile = FreeFile
  
  sDTDFile = Application.GetOpenFilename("DTD Files,*.XML", , _
  "Browse for file to be imported")
  
  If sDTDFile = False Then Exit Sub '(user cancelled import file browser)
  
  
  Open sDTDFile For Input Access Read As #ffile
    Lines = Split(Input$(LOF(ffile), #ffile), vbNewLine)
  Close #ffile
  
  Cells(1, 2) = "From DTD"
  J = 2
  
  For i = 0 To UBound(Lines)
  
    'Debug.Print "Line"; i; "="; Lines(i)
  
    With Reg1
        '.Pattern = "(\&lt;\!ELEMENT\s)(\w*)(\s*\(\#\w*\)\s*\&gt;)"
        .Pattern = "(\&lt;\!ELEMENT\s)(\w*)(\s*\(\#\w*\)\s*\&gt;)"
  
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
    End With
  
    If Reg1.Test(Lines(i)) Then
      Set M1 = Reg1.Execute(Lines(i))
      For Each M In M1
        sExtract = M.SubMatches(1)
        sExtract = Replace(sExtract, Chr(13), "")
        Cells(J, 2) = sExtract
        J = J + 1
        'Debug.Print sExtract
      Next M
    End If
  Next i
  
  Set Reg1 = Nothing
  
  End Sub
  </code></pre>
  
  <p>Currently, I'm matching on a set of data like this:</p>
  
  <pre><code> &lt;!ELEMENT DealNumber  (#PCDATA) &gt;
  </code></pre>
  
  <p>and extract Dealnumber but now, I need to add another match on data like this:</p>
  
  <pre><code>&lt;!ELEMENT DealParties  (DealParty+) &gt;
  </code></pre>
  
  <p>and extract just Dealparty without the Parens and the + </p>
  
  <p>I've been using this as a reference and it's awesome but I'm still a bit confused. <a href="https://stackoverflow.com/questions/22542834">How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops</a></p>
  
  <p><strong>EDIT</strong></p>
  
  <p>I have come across a few new scenarios that have to be matched on. </p>
  
  <pre><code> Extract Deal
   &lt;!ELEMENT Deal  (DealNumber,DealType,DealParties) &gt;
  
   Extract DealParty the ?,CR are throwing me off
   &lt;!ELEMENT DealParty  (PartyType,CustomerID,CustomerName,CentralCustomerID?,
             LiabilityPercent,AgentInd,FacilityNo?,PartyReferenceNo?,
             PartyAddlReferenceNo?,PartyEffectiveDate?,FeeRate?,ChargeType?) &gt;
  
   Extract Deals
   &lt;!ELEMENT Deals  (Deal*) &gt;
  </code></pre>