title: Regex extract data before certain text
body: <p>I have large text documents that has some data I want to be extracted.</p>
  
  <p>As you can see in a screenshot <img src="https://s7.postimg.org/3vmq6c0x7/Ashampoo_Snap_2017.04.13_15h55m01s_001.png" alt="">, I want to extract <strong>A040</strong> to excel column next to the filename.</p>
  
  <p>Before the <strong>A040</strong> there is always three empty spaces and than text <strong>Sheet</strong> (also in screenshot)</p>
  
  <p>Every file has different number and there is always letter A with three digits and text Sheet. --> example file uploaded: <img src="https://files.fm/u/3vwa9wk3" alt=""></p>
  
  <p>I has something already in VB with Excel but it is not working.</p>
  
  <pre><code>Dim cell As Range
      Dim rng As Range
      Dim output As String
  
      Set rng = ws.Range("A1", ws.Range("A1").SpecialCells(xlLastCell).Address)
  
      For Each cell In rng
  
          On Error Resume Next
          output = ExtA(cell.Value)
  
          If Len(output) &gt; 0 Then
              Range("B" &amp; j) = output
              Exit For
          End If
      Next
  
      j = j + 1
      ws.Cells.ClearContents
      'Call DelConns
  
      strFileName = Dir 'next file
  
  Loop
  End Sub
  
  Function ExtA(ByVal text As String) As String
  'REGEX Match VBA in excel
  
  Dim result As String
  Dim allMatches As Object
  Dim RE As Object
  Set RE = CreateObject("vbscript.regexp")
  
  RE.Pattern = "(?&lt;=Sheet)[^Sheet]*\ Sheet"
  RE.Global = True
  RE.IgnoreCase = True
  Set allMatches = RE.Execute(text)
  
  If allMatches.Count &lt;&gt; 0 Then
      result = allMatches.Item(0).submatches.Item(0)
  End If
  
  ExtA = result
  
  End Function
  </code></pre>