Delete all text in a sheet except e-mail addresses for MailChimp purposes

[MajidAl]

Hi all,

Does anybody here know whether it's possible to run some kind of script or macro to delete all text in an excel sheet except for the e-mail addresses in it? I have exported a mailing list from MailChimp, but I can't import it anymore, because the export made a very messy file. This is an example of what I have.

<style><!--table    {mso-displayed-decimal-separator:"\,";    mso-displayed-thousand-separator:"\.";}@page    {margin:.75in .7in .75in .7in;    mso-header-margin:.3in;    mso-footer-margin:.3in;}td    {padding-top:1px;    padding-right:1px;    padding-left:1px;    mso-ignore:padding;    color:black;    font-size:12.0pt;    font-weight:400;    font-style:normal;    text-decoration:none;    font-family:Calibri, sans-serif;    mso-font-charset:0;    mso-number-format:General;    text-align:general;    vertical-align:bottom;    border:none;    mso-background-source:auto;    mso-pattern:auto;    mso-protection:locked visible;    white-space:nowrap;    mso-rotate:0;}--></style>[TABLE="width: 87"]
<tbody>[TR]
[TD="width: 87"]First Name,"Last  Name","Email  Address",MEMBER_RATING,OPTIN_TIME,OPTIN_IP,CONFIRM_TIME,CONFIRM_IP,LATITUDE,LONGITUDE,GMTOFF,DSTOFF,TIMEZONE,CC,REGION,LAST_CHANGED,LEID,EUID,NOTES[/TD]
[/TR]
[TR]
[TD],,jp_55@hotmail.co.uk,2,,,"2016-08-23  08:03:14",185.92.223.204,,,,,,,,"2016-08-23  08:03:14",114852441,41260214da,[/TD]
[/TR]
[TR]
[TD],,Rucksana4567@gmail.com,2,,,"2016-08-27  13:43:09",185.92.223.204,,,,,,,,"2016-08-27  13:43:09",114951541,126badcb6b,[/TD]
[/TR]
[TR]
[TD],,amaanh68@gmail.com,2,,,"2016-08-28  08:03:49",185.92.223.204,,,,,,,,"2016-08-28  08:03:49",114954829,982835bc1a,[/TD]
[/TR]
</tbody>[/TABLE]

So what I want, is to delete ALL the text except for the mail addresses. Does anybody know if this is possible? I'm using Excel on a Macbook Pro.

 

[AliGW]

Please explain exactly what you mean by "doesn't work". What result do you get in B24? Again, provide an updated file - as you can see, doing so yields much quicker solutions!!!

 

[MARK858]

@MajidAl, please see the link below which uses the same formula I posted earlier on your last file.

https://app.box.com/s/zu8x6o6jei68dvw9lmiopkxoj5fr1ey0

 

[MajidAl]

You're right, posting a file DID work a lot quicker So hereby I uploaded the file: https://www.dropbox.com/s/qv1jeh1mw0jjzm4/test2.xlsx?dl=0

What I mean with "don't work" is that the cells from B24 onwards stay empty and don't copy the e-mail addresses out of the A columns anymore. My guess is that it has something to do with that every row from A24 onwards contain double, but identical e-mail addresses so Excel doesn't what to do or something?

Please explain exactly what you mean by "doesn't work". What result do you get in B24? Again, provide an updated file - as you can see, doing so yields much quicker solutions!!!

 

[AliGW]

Before we go any further, please review and respond to post #32.

 

[MajidAl]

Dear Mark,

Thank you for your help, the formula is working now! Except I ran into another problem.. See my comment earlier.

@MajidAl, please see the link below which uses the same formula I posted earlier on your last file.

https://app.box.com/s/zu8x6o6jei68dvw9lmiopkxoj5fr1ey0