Extracting email address from various data
Example of data:
H: (111) 222-3333 W: (111) 222-3333 Cel: (111) 222-3333 W Fax: (111) 222-3333 Email: ----------------------------------------------------------------------------------- H: (444) 555-6666 W: (444) 555-6666 Email: 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". |
Extracting email address from various data
If you had your data in column B, type this in A1:
=RIGHT(B1,LEN(B1)-FIND("Email: ",B1)-6) and copy it down "Wade" wrote: Example of data: H: (111) 222-3333 W: (111) 222-3333 Cel: (111) 222-3333 W Fax: (111) 222-3333 Email: ----------------------------------------------------------------------------------- H: (444) 555-6666 W: (444) 555-6666 Email: 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". |
Extracting email address from various data
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 --------------- Jacob Skaria "Wade" wrote: Example of data: H: (111) 222-3333 W: (111) 222-3333 Cel: (111) 222-3333 W Fax: (111) 222-3333 Email: ----------------------------------------------------------------------------------- H: (444) 555-6666 W: (444) 555-6666 Email: 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". |
All times are GMT +1. The time now is 07:57 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com