Extracting Information

**ONGExcel** · 11-27-2007, 04:12 AM

Hi there - wonder if anyone can help me!

I have a spreadsheet of bounce backs from a mailing list that I need to get cleaned up to be able to import the e-mail addresses onto a database. Now it all would seem quite easy - except that the export does not contain a list of the mail addresses that the e-mails were sent to. The addresses appear in the message - so they form part of the complete message. Now it would be quite easy to use a splitting formula - but there is no consistency in the mail body - different servers throw out different messages.

Is there a specific formula that picks up @ and whatever is on either side? Does anyone know a formula like that? Any help would be much appreciated.

Thanks!

**Gary Brown** · 11-27-2007, 06:13 AM

Assuming your text is in cell A1 and the email address has spaces before and after the email address i.e. xxxxx xxxxx xxx [email protected] xxxxx xxxxx

In cell B1 put the following formula:-

=(SUBSTITUTE(LEFT(A1,FIND(MID(A1,FIND("@",A1),SEARCH(" ",A1,FIND("@",A1))-FIND("@",A1)),A1))," ","@",LEN(LEFT(A1,FIND(MID(A1,FIND("@",A1),SEARCH(" ",A1,FIND("@",A1))-FIND("@",A1)),A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(MID(A1,FIND("@",A1),SEARCH(" ",A1,FIND("@",A1))-FIND("@",A1)),A1))," ",""))))

In cell C1 put the following formula:-

=MID(A1,FIND("@",B1,1)+1,SEARCH(" ",A1,FIND("@",A1))-FIND("@",B1,1)-1)

This should then give you [email protected] in cell C1

**ONGExcel** · 11-27-2007, 07:42 AM

Hi Gary,

Thanks for that - what magic! How do you come up with these formulae? Will keep picking your brain for future dilemmas I sit with!

Thanks again!

Extracting Information

LinkBack

Thread Tools

Rate This Thread

Display

Extracting Information

You rock!

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions