Here's something that will extract an e-mail address
surrounded by extraneous characters on both sides:
=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)
However, it fails if the cell only has extraneous
characters on one side. Example:
Works: adf4 (e-mail address removed) 34567 df#$
Fails: adf4 (e-mail address removed)
Fails: (e-mail address removed) 34567 df#$
Rather than expanding a formula that is already
ridiculously long, my suggestion would be to ensure all
cells have extraneous characters on both sides by using:
="a "&A31&" a"
for all cells. Then my formula will work for all
situations.
HTH
Jason
Atlanta, GA
-----Original Message-----
Please forgive this neophyte - I have 1000's of cells containing several
pieces of text, one of which is an e-mail address [addresses in no
particular order]. My two questions are:
(i) how can I copy the e-mail addresses only?
(ii) how can I match the copied e-mail addresses to the original cell?
[i don't want to do copy/paste].
many thanks, cathal...
------------------------------------------------
~~View and post usenet messages directly from
http://www.ExcelForum.com/
~~Now Available: Financial Statements.xls, a step by step
guide to creating financial statements
