Scenario: You have an excel (CSV) worksheet of undeliverable emails. You need to pull only the email address out of the body of the message. Note: I replaced all carriage returns internal to the cell value by doing a Find and Replace on: <Control J> and replace with .
Example: Cell Value A1:
Delivery has failed to these recipients or groups:
.
.bob@xyz.net<mailto:bob@xyz.net>
. A problem occurred while delivering this message to this email address. Try sending this message again.
Solution:
Use the following Excel Formulas to remove all characters to the left and to the right of the < and >.
In cell B1, remove everything to the left of the ‘<‘ in A1.
=RIGHT(A1,LEN(A1)-FIND(“<“,A1))
Result: mailto:bob@xyz.net>
. A problem occurred while delivering this message to this email address. Try sending this message again.
In cell C1, remove everything to the right of the ‘>’ in cell B1.
=LEFT(B1,FIND(“>”,B1)-1)
Result: mailto:bob@xyz.net
Ex-Shell 