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.

 

[snjpverma]

does this work ?

=IFERROR(SUBSTITUTE(MID(SUBSTITUTE(A1;";";REPT("~";99));SEARCH("@";SUBSTITUTE(A1;";";REPT("~";99)))-50;90);"~";"");"")

<tbody>
</tbody>

 

[MajidAl]

There is no result because I can't execute the formula at all due to an error. Furthermore I can't provide the link in public as there is confidential information (3.200 e-mail addresses) for the company I work for.

 

[AliGW]

What's the error message? Try substituting all the semi-colons in the formula in post #11 with commas - does that resolve the issue?

 

[Peter_SSs]

I can't provide the link in public as there is confidential information (3.200 e-mail addresses) for the company I work for.

But surely you can make up some sample dummy data with fake email addresses that demonstrate the issues you are facing & post that publicly?

 

[MajidAl]

If I use that formula I don't get an error anymore, but if I place it in B1 it says "#NAME?" in the cell I place the formula in. Can the problem be because I use a Dutch version of Excel?

does this work ?

=IFERROR(SUBSTITUTE(MID(SUBSTITUTE(A1;";";REPT("~";99));SEARCH("@";SUBSTITUTE(A1;";";REPT("~";99)))-50;90);"~";"");"")

<tbody>
</tbody>

For anyone else, this is how my file looks like (used 3 sample rows): https://www.dropbox.com/s/0uy2b1oa6080djv/test.xlsx?dl=0

 

[Peter_SSs]

Can the problem be because I use a Dutch version of Excel?

Yes. This may help you to convert it to test.

 

[AliGW]

You need to substitute the Dutch names for the functions used:

Excel function name translations - English-Dutch

 

[MajidAl]

Okay thank you guys, I guess I'm one step closer now because I don't get an error anymore. Anyway, I translated everything and pasted the formula in B1 (I'm not sure why to use this cell, the first commenter in this topic told me that). If I execute the formula, I get a blank cell and if I drag everything down to paste it in other cells everything becomes empty. Here's a screen of how it looks if I paste it in B1:

https://www.dropbox.com/s/rcgbugyimtcv61f/Schermafbeelding 2017-03-26 om 11.46.58.png?dl=0

So how B1 looks like now, is how every cell is looking like if I apply it to other cells.

 

[MARK858]

A program that might also help...

https://es.excel-translator.de/translator/

Edit: oops posted too late. Please see the previous post from the OP.

 

[MARK858]

With the English version of the formula I get the results from A8 down with the formula in post #11 (ignore the * in A8 it is a blank cell, it is just an issue Excel jeanie sometimes has)
Are you sure that you have translated it properly?

Excel Workbook
	A
1	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
2	,,jp_5sdfdsfds5@hotmail.co.uk,2,,,"2016-08-23 08:03:14",185.92.223.204,,,,,,,,"2016-08-23 08:03:14",114852441,41260214da,
3	,,Rucksanfsdfsda4567@gmail.com,2,,,"2016-08-27 13:43:09",185.92.223.204,,,,,,,,"2016-08-27 13:43:09",114951541,126badcb6b,
4	,,amadfsdsfsanh68@gmail.com,2,,,"2016-08-28 08:03:49",185.92.223.204,,,,,,,,"2016-08-28 08:03:49",114954829,982835bc1a,
8	*
9	jp_5sdfdsfds5@hotmail.co.uk
10	Rucksanfsdfsda4567@gmail.com
11	amadfsdsfsanh68@gmail.com
Blad1