Solved How to extract/retrieve email addresses from an excel sheet?

April 30, 2012 at 09:34:15
Specs: Windows 7
this is how I have all my data. there are more than 200 contact info. Everything is in column A and I'm trying to retrieve only the email addresses and paste them in another column. How can I separate the email address????

COMPANY NAME
ADDRESS
Phone:
Fax:
Email: test@test.com
Web: www.test.com

I have excell 2010


See More: How to extract/retrieve email addresses from an excel sheet?

Report •


#1
April 30, 2012 at 10:07:01
Enter this in B1 and drag it down as far as you need:

=IF(LEFT(A1,5)="Email", RIGHT(A1,LEN(A1)-7),"")

Select Column B and do a Copy...PasteSpecial...Values to eliminate the formulas and leave the text.

Sort Descending to bring all of the email addresses to the top of Column B.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 30, 2012 at 11:52:40
Thanks it worked!!!... but i just realized. Not all of them have the word "Email" infront of them!! :(

Do I just need to type the word email in each one of them? Is there any way I can also retrieve the ones that don't only say test@test.com
This is how some of them look

ADDRESS
Phone
E-mail Address
info@info.com
Company Website
http://www.company.com


Report •

#3
April 30, 2012 at 13:18:15
✔ Best Answer
It gets a bit difficult to extract text when we don't have a consistant starting point. Excel was never built to be a text editor.

Anyway, if the only two situations you have are the two you've described, and they are exactly as you have described, try this:

Put this in B1 and drag it down:

=IF(ISERROR(FIND("@",A1)),"",A1)

Select Column B and do a Copy...PasteSpecial...Values to eliminate the formulas and leave the text.

With Column B still selected, do an Edit...Replace...
Find what: "Email: " (no quotes, but include the trailing space)
Replace with: leave this field empty

Sort Descending to bring all of the email addresses to the top of Column B.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
May 1, 2012 at 07:11:16

I FIGURED IT OUT! It was difficult, it took me a while but I did it. Thanks to you!

Now i have them all in column B .... THANK YOU! thank you for your help :)


Report •

#5
Report •

Ask Question