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.
Related Posts
Dear Neale, Great!
You could have alternatively used Mid$ instead of Mid, right? Something that I learnt from you.
Hi Sandeep – Yes Mid$ works too.