Remove some text from a cell
 

I am attempting to update an email list. I have exported from Outlook all of
the emails that were returned to me because of a bad email address. Some of
the address' are in a column along with a bunch of other words. My goal is
to "pull" only the email address' out of that column. The only thing that I
see that is consistent in each cell is the @ of the email address.

Your help is greatly appreciated.....

Remove some text from a cell
 

Hi!

Try this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT("
",LEN(A1))),LEN(A1)))

Seems to work on the following possibilities:

please note that Joe<atMicrosoft.com is no longer
Bob<atAol.com email is undeliverable
if you can read this buffy<atnetscape.net
Tom01<atcomcast.rr text text
me<atyou.org

Note: I've replaced the "at" sign so the examples won't hyperlink.

Biff

"JW" wrote in message
...
I am attempting to update an email list. I have exported from Outlook all
of
the emails that were returned to me because of a bad email address. Some
of
the address' are in a column along with a bunch of other words. My goal
is
to "pull" only the email address' out of that column. The only thing that
I
see that is consistent in each cell is the @ of the email address.

Your help is greatly appreciated.....

Remove some text from a cell
 

Well, I can see that line wrap is going to cause problems with formula so
here it is in chunks:

=TRIM(RIGHT(SUBSTITUTE(LEFT
(A1,FIND(" ",A1&" ",FIND("@",A1))-1)
," ",REPT(" ",LEN(A1))),LEN(A1)))

Biff

"Biff" wrote in message
...
Hi!

Try this:

=TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(" ",A1&" ",FIND("@",A1))-1)," ",REPT("
",LEN(A1))),LEN(A1)))

Seems to work on the following possibilities:

please note that Joe<atMicrosoft.com is no longer
Bob<atAol.com email is undeliverable
if you can read this buffy<atnetscape.net
Tom01<atcomcast.rr text text
me<atyou.org

Note: I've replaced the "at" sign so the examples won't hyperlink.

Biff

"JW" wrote in message
...
I am attempting to update an email list. I have exported from Outlook all
of
the emails that were returned to me because of a bad email address. Some
of
the address' are in a column along with a bunch of other words. My goal
is
to "pull" only the email address' out of that column. The only thing
that I
see that is consistent in each cell is the @ of the email address.

Your help is greatly appreciated.....