Extract Email from Copied Outlook Address

Macro to the rescue

I frequently copy an email address from Outlook to Excel and most times it looks like John Smith<jsmith@email.com>. To be used as an email I need to extract from between < and >. To do that in a single cell is tedious, so I wrote a macro to do it for me.

With the cell selected running this macro will replace the entry with the email address. It only works on the first cell in the range if more than one cell is selected.

It checks first to confirm the Selection is a range, and then that both < and > are in the string.

Sub ExtractEmail()
 
'in the selected cell this macro
 
'converts the structure john smith<jsmith@email.com> to jsmith@email.com
 
If TypeName(Selection) = "Range" Then       
 
  If InStr(Selection(1).Value, "<") > 0 And _
         InStr(Selection(1).Value, ">") > 0 Then
 
      Selection(1).Value = Mid(Selection(1).Value, _
         InStr(Selection(1).Value, "<") + 1, _ 
         InStr(Selection(1).Value, ">") - InStr(Selection(1).Value, "<") - 1)
 
  End If
 
End If
 
End Sub

The Mid command is the same as Excel’s MID cell function.

The InStr command is the same as Excel’s SEARCH cell function, but it returns zero if the entry isn’t found – much better than SEARCH which returns an error.

Update November 22, 2022

Thanks to Rick Rothstein in the Comments section for this more elegant solution to the email extraction.

Selection(1) = Split(Replace(Selection(1),”>”,”<“),”<“)(1)

This replaces the three lines of code in my example.

 

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

5 thoughts on “Extract Email from Copied Outlook Address

  1. Dear Neale, Great!
    You could have alternatively used Mid$ instead of Mid, right? Something that I learnt from you.

  2. Just noting that you can replace this line of code…

    Selection(1).Value = Mid(Selection(1).Value, _
    InStr(Selection(1).Value, “”) – InStr(Selection(1).Value, “”, “<"), "<")(1)

  3. Something went wrong with my first attempt to post this (I think the less than symbols may have been the problem), so I’ll try again…

    Just noting that you can replace the line of code inside the inner If..Then statement with this short line of code…

    Selection(1) = Split(Replace(Selection(1),”>”,”<"),"<")(1)