Need a formula to remove an email address from a long string of text.

Hi, i need a formula to remove an email address from a long string of text, i exported my hotmail account contacts on to a spreadsheet but each contact has been put in to 1 cell e.g.




A `
1 Contact 1 [email protected] 123 contact street
2 Contact 2 [email protected] 1233 contact street

etc
etc.

=trim(replace(substitute(a1," ",rept(" ",99)),find("@",substitute(a1," ",rept(" ",99)))-50,150,""))

Do you mean that you want to end up with this:

C
1 Contact 1 123 contact street
2 Contact 2 1233 contact street

where the data is still contained within one cell but with the email address removed, or do you want the data to appear in two separate columns, like this:

C ..................D
1 Contact 1 ....123 contact street
2 Contact 2 ....1233 contact street

Hope this helps.

Pete

Hi Pete,

I would like to just have the email address in the column, none of the other details.

i see the confusion, i didn't propose the problem well enough haha.

Teethless mama - how can i make it so it takes out everything else other than the emaila addresses?

No problem....

=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("@",SUBSTITUTE(A1," ",REPT(" ",99)))-50,99))

thanks also, on a related note. i have characters in there i don't want, there are loads of commas, quote marks and a SMTP. how can i remove them? (doesn't have to be in 1 formula)