Excel - getting email addresses from cells

_Andy_

Hi

I have a list of contacts. Each cell holds the information - First name Last Name Email, e.g

Bob Smith [EMAIL="bob@bobsmith.com"]bob@bobsmith.com[/EMAIL]

I have 100 or so which I need to mail merge.

What formula would I need to end up with a column that just has the email addresses?

I'm guessing I could use the RIGHT function but haven't used it before so could use some help.

Cheers

Edit - Using Excel 2003 by the way.

kevsan

Are there spaces between the name etc? If so, you might find it easier to do text to column funtion with a delimited file type and select space as the delimiter.

Should do what you want and be a lot easier than a formula.

_Andy_

kevsan wrote: »

Are there spaces between the name etc? If so, you might find it easier to do text to column funtion with a delimited file type and select space as the delimiter.

Should do what you want and be a lot easier than a formula.

Yes the content in each cell is (without quotes) is like

"Bob Smith bobsmith@bob.com"

How would I do what you suggested?

Thanks

kevsan

Highlight the cells you want to split and go to the text to column function (this is under the data tab in excel 2007 & in the data menu in 2003)
Click on delimited and next, then select the spaces option, click finish, it should create 3 new columns next to the originals with the new information.

Most of that is from memory, but you get the gist!!

_Andy_

Thanks kevsan

Just tried that but it gives me one column which is the first name the only. The first name it has made into an email hyperlink which is no good as I need to mail merge from the list of email addresses.

Cheers

peteb23

try the following formula in the next column (assuming your data is in column A)

=TRIM(MID(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",40)), FIND(REPT("@",COLUMNS($A1:A1)),SUBSTITUTE(SUBSTITUTE(" "&$A1&" "," ",REPT(" ",40)),"@",REPT("@",COLUMNS($A1:A1)),COLUMNS($A1:A1)))-40,80))

Not my code but one I've used in the past

EDIT just tried the Text to Column solution (space delimiter) and that worked fine too with your sample data.

Strider590

I can't try this right now, but the first thing id try would be:

Select the column and copy

Paste into notepad

Select all text and hit "ctrl+H"

Put a space into "find what" and put "ZZ" into "replace with"

You'll now have ZZ in where the spaces where

Copy back into Excel

Now hit "ctrl+H" again and type in *ZZ

Replace with nothing and hit replace all

All the names will have been deleted, leaving just the email addresses


-Edit - Just tried it and it does work

_Andy_

I'm having no joy sadly!

Tried the ZZ, when I went to find/replace in Excel it says it can't find ZZ or *ZZ

Tried the formula and it gave me surname email address.

Thanks

peteb23

Can you post some actual examples of the data because it does work for me with the "Bob Smith bob@bobsmith.com" data[EMAIL="bob@bobsmith.com"]
[/EMAIL]

Strider590

No it works seriously, try it again lol

Paste the list with the spaces replaced, back to excel from notepad then select that new column and do the replace *ZZ with nothing.

Only using ZZ because unless you have "Buzz Lightyear" on your email list, nobody will have ZZ in the name

* is the wildcard function, by the way.

peteb23

Strider590 wrote: »

Only using ZZ because unless you have "Buzz Lightyear" on your email list, nobody will have ZZ in the name

Or ZZ Top