title: RegEx to extract email
body: <p>I need to extract only the email from a spreadsheet in Excel. I've found some example VBA code here at this StackOverflow <strong><a href="https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops">link</a></strong>, courtesy of <a href="https://stackoverflow.com/users/2521004/portland-runner">Portland Runner</a>.</p>
  
  <p>I created an Excel module and it seems to be working fine, except it only returns the first uppercase character of the address into the cell and it's ignoring the email.</p>
  
  <p>For example:</p>
  
  <pre><code>Text                                    | Result
  ----------------------------------------|------------------------------
  My email address is address@gmail.com   | My email address is  
  Yes  Address@gmail.com                  | Yes  A
  </code></pre>
  
  <p>Below is the code I'm using:</p>
  
  <pre><code>Function simpleCellRegex(Myrange As Range) As String
      Dim regEx As New RegExp
      Dim strPattern As String
      Dim strInput As String
      Dim strReplace As String
      Dim strOutput As String
  
  
      strPattern = "[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
  
      If strPattern &lt;&gt; "" Then
          strInput = Myrange.Value
          strReplace = ""
  
          With regEx
              .Global = True
              .MultiLine = True
              .IgnoreCase = False
              .Pattern = strPattern
          End With
  
          If regEx.test(strInput) Then
              simpleCellRegex = regEx.Replace(strInput, strReplace)
          Else
              simpleCellRegex = "Not matched"
          End If
      End If
  End Function
  </code></pre>
  
  <p>I do not have enough experience with VBA to really diagnose what might be happening here, hopefully someone will be able to spot what I'm doing wrong.</p>
  
  <h1>Working Code</h1>
  
  <pre><code>Function simpleCellRegex(Myrange As Range) As String
  Dim regEx As New RegExp
  Dim strPattern As String
  Dim strInput As String
  Dim strReplace As String
  Dim strOutput As String
  
  
  strPattern = "[A-Za-z0-9!#$%&amp;'*+/=?^_`{|}~-]+(?:\.[a-z0-9!#$%&amp;'*+/=?^_`{|}~-]+)*@(?:[a-z0-9](?:[a-zA-Z0-9-]*[a-z0-9])?\.)+[a-z0-9](?:[a-z0-9-]*[a-z0-9])?"
  
  If strPattern &lt;&gt; "" Then
      strInput = Myrange.Value
      strReplace = ""
  
      With regEx
          .Global = True
          .MultiLine = True
          .IgnoreCase = True
          .Pattern = strPattern
      End With
  
      If regEx.Test(strInput) Then
          Set matches = regEx.Execute(strInput)
          simpleCellRegex = matches(0).Value
      Else
          simpleCellRegex = "Not matched"
      End If
  End If
  End Function
  </code></pre>