Extracting email address from various data

[Wade]

Example of data:

H: (111) 222-3333 W: (111) 222-3333 Cel: (111) 222-3333 W Fax: (111)
222-3333 Email: (e-mail address removed)
-----------------------------------------------------------------------------------
H: (444) 555-6666 W: (444) 555-6666 Email: (e-mail address removed) W Fax: (444)
555-6666

Example = 1 column and 2 rows of data and I am trying to extract the email
address and place it into a single column without cut and paste 5000 times.

The text to columns feature works but spread the addresses haphazardly
across 20+ columns. That leads to a new question, I want the email in column
A and they are spread out B-Z. How do I get them all in column A without C&P.
I tried "=B1-Z1" in A1 and got "#VALUE".

 

[Sam Wilson]

If you had your data in column B, type this in A1:

=RIGHT(B1,LEN(B1)-FIND("Email: ",B1)-6)

and copy it down

 

[Jacob Skaria]

With data in A1 try the below formula in B1 and copy that down as required

=MID(A1,FIND("Email:",A1)+7,FIND("W Fax:",A1)-FIND("Email:",A1)-7)

If this post helps click Yes