In one of the columns I have text with email addresses I need seperated.

Amongst the text (i.e. "please note that is no longer
employeed" - or - email is undeliverable")

Is there a formula that I can create to single out just the email address?
There's no set # of characters before or after the "@" - but I'm assuming I
could go by a SPACE to seperate the name@domain from the rest of the text.

Any suggestions?

The trim function only works in one direction (i.e. after the @) and I need
it to go both ways.

Thanks,
Joey

You could do Data TextToColumns using SPACE as a delimiter and it should
break the entire email address out into it's own separate column as it has a
space both before and after with none in between.....

Vaya con Dios,
Chuck, CABGx3



"Joey" wrote:

In one of the columns I have text with email addresses I need seperated.

Amongst the text (i.e. "please note that is no longer
employeed" - or - email is undeliverable")

Is there a formula that I can create to single out just the email address?
There's no set # of characters before or after the "@" - but I'm assuming I
could go by a SPACE to seperate the name@domain from the rest of the text.

Any suggestions?

The trim function only works in one direction (i.e. after the @) and I need
it to go both ways.

Thanks,
Joey

But that doesn't put the email address in one column, especially if there's
25 words in the text of the message. Text-to-columns using SPACE could just
spread out the message along 25 more columns, with NO idea where the email
address is.

I even tried text-to-columns with "@" as the seperator, but then I don't get
the name or the domain name.

Joey

"CLR" wrote:

You could do Data TextToColumns using SPACE as a delimiter and it should
break the entire email address out into it's own separate column as it has a
space both before and after with none in between.....

Vaya con Dios,
Chuck, CABGx3



"Joey" wrote:

In one of the columns I have text with email addresses I need seperated.

Amongst the text (i.e. "please note that is no longer
employeed" - or - email is undeliverable")

Is there a formula that I can create to single out just the email address?
There's no set # of characters before or after the "@" - but I'm assuming I
could go by a SPACE to seperate the name@domain from the rest of the text.

Any suggestions?

The trim function only works in one direction (i.e. after the @) and I need
it to go both ways.

Thanks,
Joey

With your data in A1 try this formula

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=523235

On Thu, 16 Mar 2006 14:03:01 -0600, daddylonglegs
wrote:


With your data in A1 try this formula

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

Doesn't seem to work if the email address is at the very end of the string.
--ron

Ron Rosenfeld Wrote:

Doesn't seem to work if the email address is at the very end of the
string.
--ron

Hi ron, quite right. I believe that can be fixed with a small
amendment

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

Biff, thanks also. I didn't consider multiple email addresses, I'll
leave that to somebody else ;)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=523235

Crashes on entries like:

text text text text


Biff

"daddylonglegs"
wrote in message
news:daddylonglegs.24s3qp_1142539806.708@excelforu m-nospam.com...

With your data in A1 try this formula

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=523235

This seems to work:

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

Biff

"Biff" wrote in message
...
Crashes on entries like:

text text text text


Biff

"daddylonglegs"
wrote in
message news:daddylonglegs.24s3qp_1142539806.708@excelforu m-nospam.com...

With your data in A1 try this formula

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=523235

Joey, This works, too, but you need morefunc.dll (google search)

=TRIM(TEXTREVERSE(LEFT(TEXTREVERSE(LEFT(A1,(SEARCH ("
",A1,(SEARCH("@",A1,1)))-1))),(SEARCH(" ",TEXTREVERSE(LEFT(A1,(SEARCH("
",A1,(SEARCH("@",A1,1)))-1))),1)))))

Beege

"Joey" wrote in message
...
In one of the columns I have text with email addresses I need seperated.

Amongst the text (i.e. "please note that is no longer
employeed" - or - email is undeliverable")

Is there a formula that I can create to single out just the email address?
There's no set # of characters before or after the "@" - but I'm assuming
I
could go by a SPACE to seperate the name@domain from the rest of the text.

Any suggestions?

The trim function only works in one direction (i.e. after the @) and I
need
it to go both ways.

Thanks,
Joey