Function to convert to email address?

[charlie.wade]

Given a column of cells each with text in the format:

LAST NAME, FIRST NAME

..... like "Jones, Bob" and "Johnson, David", is there a quick excel
function/method that will convert and then write them to a different
column such that:

(first initial)(last name)@xyz.com

.... so "Jones, Bob" becomes "(e-mail address removed)" and "Johnson, David"
becomes "(e-mail address removed)"?

TIA

 

[Nick Hodge]

Charlie

Technically you may need this

=HYPERLINK("mailto:"&LOWER((MID(A1,FIND(",",A1,1)+2,1))&LEFT(A1,FIND(",",A1,1)-1))&"@xyz.com",LOWER((MID(A1,FIND(",",A1,1)+2,1))&LEFT(A1,FIND(",",A1,1)-1))&"@xyz.com")

You could drop the lower if the case is not important and the repeating part
if you can put up with mailto:[email protected] rather than (e-mail address removed)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html

 

[Niek Otten]

=MID(A1,FIND(" ",A1)+1,1)&LEFT(A1,FIND(" ",A1)-2)&"@xyz.com"

I don't know how to get rid of the underlines, sorry!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| Given a column of cells each with text in the format:
|
| LAST NAME, FIRST NAME
|
| .... like "Jones, Bob" and "Johnson, David", is there a quick excel
| function/method that will convert and then write them to a different
| column such that:
|
| (first initial)(last name)@xyz.com
|
| ... so "Jones, Bob" becomes "(e-mail address removed)" and "Johnson, David"
| becomes "(e-mail address removed)"?
|
| TIA
|

 

[charlie.wade]

Charlie

Technically you may need this

=HYPERLINK("mailto:"&LOWER((MID(A1,FIND(",",A1,1)+2,1))&LEFT(A1,FIND(",",A1,1)-1))&"@xyz.com",LOWER((MID(A1,FIND(",",A1,1)+2,1))&LEFT(A1,FIND(",",A1,1)-1))&"@xyz.com")

You could drop the lower if the case is not important and the repeating part
if you can put up with mailto:[email protected] rather than (e-mail address removed)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web:www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @http://www.exceluserconference.com/UKEUC.html

Awesome, awesome, awesome. Thank you very much, it works like a charm.

-CW

 

[Nick Hodge]

Charlie

Glad it worked.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
web: www.nickhodge.co.uk

UK Excel User Conference In Cambridge
November 28th - December 1st 2007
Register Now @ http://www.exceluserconference.com/UKEUC.html