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.

 

[MajidAl]

That's weird, seems to be working perfect for you.. Maybe I should try in an english version of Excel, but I'm pretty sure I translated everything correctly, also according to the tool you provided me. So in baby language (I'm sorry but I'm really bad with formulas and functions in Excel..)

1. Copy the formula provided in post #11
2. Paste it in B1 (a cell which is empty anyway, because my Excel file consists of one column and multiple rows anyway)
3. Cell B1 is still empty but contains the formula and then drag the cell down to cells A2 till XXXX)

Is this correct?

With the English version of the formula I get the results from A9 down with the formula in post #11.
Are you sure that you have translated it properly?


Blad1

*	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

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:88px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula A8 =IFERROR(SUBSTITUTE(MID(SUBSTITUTE(A1,",",REPT("~",99)),SEARCH("@",SUBSTITUTE(A1,",",REPT("~",99)))-50,90),"~",""),"") A9 =IFERROR(SUBSTITUTE(MID(SUBSTITUTE(A2,",",REPT("~",99)),SEARCH("@",SUBSTITUTE(A2,",",REPT("~",99)))-50,90),"~",""),"") A10 =IFERROR(SUBSTITUTE(MID(SUBSTITUTE(A3,",",REPT("~",99)),SEARCH("@",SUBSTITUTE(A3,",",REPT("~",99)))-50,90),"~",""),"") A11 =IFERROR(SUBSTITUTE(MID(SUBSTITUTE(A4,",",REPT("~",99)),SEARCH("@",SUBSTITUTE(A4,",",REPT("~",99)))-50,90),"~",""),"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[AliGW]

Instead of a DropBox link to an image, why not put the workbook on DropBox???

 

[MajidAl]

Dear AliGW,

I posted a link earlier of some sample data. It's exactly like my original workbook, but with fake e-mail addresses and just 4 rows. Could you make this file work? And can I provide you with the full workbook in a message to look at my file? I would greatly appreciate it, because I'm a little lost here.. Thanks for the help everyone that's helping me by the way!

https://www.dropbox.com/s/0uy2b1oa6080djv/test.xlsx?dl=0

Instead of a DropBox link to an image, why not put the workbook on DropBox???

 

[MARK858]

All I done was paste the formula in A8, changed the ; to , as that is my default separator and dragged the formula down.

According to the program in the link I posted the Dutch version should be...

=ALS.FOUT(SUBSTITUEREN(MIDDEN(SUBSTITUEREN(A1;",";HERHALING("~";99));VIND.SPEC("@";SUBSTITUEREN(A1;",";HERHALING("~";99)))-50;90);"~";"");"")

I suspect you will need to change the 2 commas within the quotes to ;
but haven't got a Dutch version to test if correct.

 

[MajidAl]

I've changed my Mac's language to English so my Excel is in English now too. I'm using the formula:

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

I'm really frustrated because it works without problems with you guys and not in my Excel.. Also, why do you paste the formula in A8? My test file consists of 4 rows only and stops at A4. Wouldn't the formula have no effect if you drag it down from A8 because everything below A4 is empty? Anyway, still empty cells if I copy and paste the formula and apply it to other cells.... Is there someone who I can send the file to have a look for me? I'm getting desperate...

All I done was paste the formula in A8, changed the ; to , as that is my default separator and dragged the formula down.

According to the program in the link I posted the Dutch version should be...



I suspect you will need to change the 2 commas within the quotes to ;
but haven't got a Dutch version to test if correct.

 

[MARK858]

Also, why do you paste the formula in A8

Just because it was easier to display on the forum.

Is there someone who I can send the file to have a look for me? I'm getting desperate

Not to me I am afraid as I don't give out my email address (besides I thought the data was confidential).

Wouldn't the formula have no effect if you drag it down from A8 because everything below A4 is empty

No the formula in A8 references A1, A9 references A2 etc.

 

[AliGW]

Is there someone who I can send the file to have a look for me? I'm getting desperate...

No, as this would be contrary to the spirit of the forum. What would be far simpler would be for you to give us a few lines of dummy data (as Peter suggested earlier) in a file made available on DropBox (or similar) that shows the issues you are experiencing for us to see. Otherwise we are going to be playing forum ping-pong all day.

 

[MajidAl]

Dear AliGW,

Hereby the file with dummy data, where I've inserted the formula in B1 until B4, with no result as you can see, because rows A1 - A4 stay the same. You have to download the file to see everything, because Dropbox preview isn't exactly like Excel.

https://www.dropbox.com/s/0uy2b1oa6080djv/test.xlsx?dl=0

No, as this would be contrary to the spirit of the forum. What would be far simpler would be for you to give us a few lines of dummy data (as Peter suggested earlier) in a file made available on DropBox (or similar) that shows the issues you are experiencing for us to see. Otherwise we are going to be playing forum ping-pong all day.

 

[AliGW]

Easy fix, I think. Where this appears (twice) in your formula:

A1;";"

change it to this:

A1;","

 

[MajidAl]

FINALLY WORKED, THANK YOU SO MUCH! The only thing is I've encountered another problem now... I successfully did this for my complete file for rows A1 - A23. From A24 I seem to be getting problems, and I think because from there onwards there is a double e-mail address problem in every row. So for example, A23 (which works looks like this:

[TABLE="width: 1256"]
<tbody>[TR]
[TD],,ludfsdsdfens@zonnet.nl,2,,,"2016-10-29 11:18:14",185.92.223.204,,,,,,,,"2016-10-29 11:18:14",116577657,e5cf8f465f,[/TD]
[/TR]
</tbody>[/TABLE]

And A24 - A2203 (which don't work) look like this:

[TABLE="width: 1256"]
<tbody>[TR]
[TD]marouane@hotmail.com,,marouane@hotmail.com,4,,,"2014-12-18 12:07:29",,51.4867000,5.6621800,1,2,Europe/Brussels,NL,NB,"2014-12-18 12:07:29",97495793,6b009070db,[/TD]
[/TR]
</tbody>[/TABLE]

So I miss out on more than 2.000 e-mail addresses of the total 3.200. Is there any fix for this?

Easy fix, I think. Where this appears (twice) in your formula:

A1;";"

change it to this:

A1;","