Advanced Find and Replace

LBrown20

New Member
Joined
Jun 17, 2009
Messages
8
Hello and thanks so much in advance for any suggestions/help.

I'm trying to manage about 200 bouncebacks from my firm's newsletter. To make the updates in our CRM tool, I need to isolate the email addresses of every bounced email and get them into one cell. So I exported the bouncebacks to Excel, and the text of the bounced email is in one cell in one column.

I noticed that bounced emails are returned with varying text, but most have a single common denominator -- somewhere in the text of the bounced email the email address will be found between the characters "<" and ">". For example: "Your message to <name@company.com> was not delivered."

I think I need to use the find and replace function to isolate the email and replace everything else. I thought [~<*~>] would work, but it doesn't. I've also tried it in 2 steps: first replacing everything before the < and then everything after it, but it doesn't work all of time.

Does anyone have any ideas? Thanks again for your help!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Hello and welcome to MrExcel.

Try this:

<b>Sheet7</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:518px;" /><col style="width:257px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >Your message to <name@company.com> was not delivered.</td><td >name@company.com</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=MID(A1,FIND<span style=' color:008000; '>("<",A1)</span>+1,FIND<span style=' color:008000; '>(">",A1)</span>-FIND<span style=' color:008000; '>("<",A1)</span>-1)</td></tr></table></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4
 

EducatedFool

Active Member
Joined
Jun 10, 2009
Messages
272
Select all cells with emails, and run the macro:

Code:
Sub [B]Remove_all_but_EMails[/B]()
    Dim cell As Range: Application.ScreenUpdating = False
    For Each cell In Selection.Cells
        If cell Like "*<*>*" Then
            txt = Mid(cell, InStr(1, cell, "<") + 1)
            txt = Left(txt, InStr(1, txt, ">") - 1): cell = txt
        End If
    Next cell
End Sub
 

LBrown20

New Member
Joined
Jun 17, 2009
Messages
8
Unbelievable...the macro works great. Can't thank you enough. This has been an unbelievable experience...I promise to pay it forward!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,710
Messages
5,597,702
Members
414,164
Latest member
ARTW

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top