Extracting email address from spreadsheet. Pls take a look.

[skuba]

Hi,
Basically, what I need to do is haveing a formula that will extract a
email address from the previous column.

In column A I will have a bunch of text including one or more emai
address.

There are two solutions that would help me a LOT.
The most simple: WIll extract the first email only and place in o
column B.
The most complicated: Will extract the first email adn put on column B
if there is a second email will put on column C, etc...

Basically, the formula needs to look on each cell on column A and fin
an @ sign. Then follow the next characters to the left and right unti
there is a space. Because if there is a space it's the end of th
email.

Does it make sense? Could you guys help me?

Thank

 

[Jason Morin]

This will remove the 1st e-mail:

=MID(SUBSTITUTE(LEFT(A1,FIND("@",A1))," ","^^^^^",LEN(LEFT
(A1,FIND("@",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND
("@",A1))," ",""))),FIND("^^^^^",SUBSTITUTE(LEFT(A1,FIND
("@",A1))," ","^^^^^",LEN(LEFT(A1,FIND("@",A1)))-LEN
(SUBSTITUTE(LEFT(A1,FIND("@",A1))," ",""))))+5,255)&LEFT
(RIGHT(A1,LEN(A1)-FIND("@",A1)),FIND(" ",RIGHT(A1,LEN(A1)-
FIND("@",A1)))-1)

And this will remove the 2nd (if there is one):

=MID(SUBSTITUTE(LEFT(A2,FIND("@",A2,FIND("@",A2)
+1))," ","^^^^^",LEN(LEFT(A2,FIND("@",A2,FIND("@",A2)+1)))-
LEN(SUBSTITUTE(LEFT(A2,FIND("@",A2,FIND("@",A2)
+1))," ",""))),FIND("^^^^^",SUBSTITUTE(LEFT(A2,FIND
("@",A2,FIND("@",A2)+1))," ","^^^^^",LEN(LEFT(A2,FIND
("@",A2,FIND("@",A2)+1)))-LEN(SUBSTITUTE(LEFT(A2,FIND
("@",A2,FIND("@",A2)+1))," ",""))))+5,255)&LEFT(RIGHT
(A2,LEN(A2)-FIND("@",A2,FIND("@",A2)+1)),FIND(" ",RIGHT
(A2,LEN(A2)-FIND("@",A2,FIND("@",A2)+1)))-1)

The only caveat to these ugly formulas is that the e-mail
address strings must have extraneous characters on both
sides. The formulas won't work on something like:

(e-mail address removed) 5#4.iuta!

or

4kg>?"]] (e-mail address removed)

HTH
Jason
Atlanta, GA