Remove some text from a cell

[JW]

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.....

[Biff]

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<at>Microsoft.com is no longer
Bob<at>Aol.com email is undeliverable
if you can read this buffy<at>netscape.net
Tom01<at>comcast.rr text text
me<at>you.org

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

Biff

"JW" <JW@discussions.microsoft.com> wrote in message
news:A6F7F8C5-7FA0-4393-9305-E494BFEE4FEB@microsoft.com...
>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.....

[Biff]

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" <biffinpitt@comcast.net> wrote in message
news:%23iiX7$XwGHA.3420@TK2MSFTNGP06.phx.gbl...
> 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<at>Microsoft.com is no longer
> Bob<at>Aol.com email is undeliverable
> if you can read this buffy<at>netscape.net
> Tom01<at>comcast.rr text text
> me<at>you.org
>
> Note: I've replaced the "at" sign so the examples won't hyperlink.
>
> Biff
>
> "JW" <JW@discussions.microsoft.com> wrote in message
> news:A6F7F8C5-7FA0-4393-9305-E494BFEE4FEB@microsoft.com...
>>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.....
>
>