title: Excel VBA + Regular Expression
body: <p>Ok, to start. I'm a little rusty on VBA, 3 + years since Ive need to use it. </p>
  
  <p>In short, im struggling to extract text from a string. Im using regular expression to extract my department name and date from this string. </p>
  
  <p>The Department will always fall between : and -.</p>
  
  <p>I can't share the document due to security. But, I can explain the format and hopefully we can work from that.</p>
  
  <p>Col A----Col B----Col C---Col D</p>
  
  <p>Date(e)--Dept(e)--String--Duration</p>
  
  <p>Where (e) means it was extracted from the string. </p>
  
  <p><a href="https://i.stack.imgur.com/QWiuJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/QWiuJ.png" alt="String Example"></a></p>
  
  <p>My code for the extraction, thus far, is below. Currently it will loop through all available rows and extract the department, but it always take the : and - with it! I can't seem to find a way to cut these out. </p>
  
  <p>Any assistance?</p>
  
  <p>I can probably work out the date bit eventually. </p>
  
  <p>The final output from this code is ": Inbound Contacts -"
  Where I need, "Inbound Contacts". </p>
  
  <pre><code>Sub stringSearch()
      Dim ws As Worksheet
      Dim lastRow As Long, x As Long
      Dim matches As Variant, match As Variant
      Dim Reg_Exp As Object
  
      Set Reg_Exp = CreateObject("vbscript.regexp")
  
      Reg_Exp.Pattern = "\:\s(\w.+)\s\-"
  
  
      Set ws = Sheet2
  
      lastRow = ws.Range("C" &amp; Rows.Count).End(xlUp).Row
  
      For x = 1 To lastRow
          Set matches = Reg_Exp.Execute(CStr(ws.Range("C" &amp; x).Value))
          If matches.Count &gt; 0 Then
              For Each match In matches
                  ws.Range("B" &amp; x).Value = match.Value
              Next match
          End If
      Next x
  End Sub
  </code></pre>