I have a list of contacts with email addresses. My client is changing their
email layout from to . I would like to
replace the jmsith with john.smith, which I can get by concatenating the
first and last name columns with a dot, but how do I get it to replace
everything up to the @ with the newly concatenated name? I tried a
combination of search, replace, etc, but I couldn't get the combo right. Any
help is appreciated.
Everything after the @ remains the same.

Thanks,
cstwoplus

CONCATENATE("FirstName", ".", "LastName", ")

If you have the entire first and last names in separate fields, you can do
this all with one concatenate function. (Concatenate
")

Hope this helps.

Bill Horton

"cstwoplus" wrote:

I have a list of contacts with email addresses. My client is changing their
email layout from to . I would like to
replace the jmsith with john.smith, which I can get by concatenating the
first and last name columns with a dot, but how do I get it to replace
everything up to the @ with the newly concatenated name? I tried a
combination of search, replace, etc, but I couldn't get the combo right. Any
help is appreciated.
Everything after the @ remains the same.

Thanks,
cstwoplus

I did not make it clear that the list I need to change has 400 names, and
they do not all end with @client.com. They all have the same layout, but the
domain name changes, so I need to keep that intact while removing everything
before the @ and replacing it with the concatenated name.
becomes
becomes
becomes

Sorry for not being clear.

cstwoplus

"William Horton" wrote:

CONCATENATE("FirstName", ".", "LastName", ")

If you have the entire first and last names in separate fields, you can do
this all with one concatenate function. (Concatenate
")

Hope this helps.

Bill Horton

"cstwoplus" wrote:

I have a list of contacts with email addresses. My client is changing their
email layout from to . I would like to
replace the jmsith with john.smith, which I can get by concatenating the
first and last name columns with a dot, but how do I get it to replace
everything up to the @ with the newly concatenated name? I tried a
combination of search, replace, etc, but I couldn't get the combo right. Any
help is appreciated.
Everything after the @ remains the same.

Thanks,
cstwoplus

="first name"&"."&"last name"&RIGHT(A1,LEN(A1)+1-FIND("@",A1))
--
David Biddulph

"cstwoplus" wrote in message
...
I did not make it clear that the list I need to change has 400 names, and
they do not all end with @client.com. They all have the same layout, but
the
domain name changes, so I need to keep that intact while removing
everything
before the @ and replacing it with the concatenated name.
becomes
becomes
becomes

Sorry for not being clear.

cstwoplus

"William Horton" wrote:

CONCATENATE("FirstName", ".", "LastName", ")

If you have the entire first and last names in separate fields, you can
do
this all with one concatenate function. (Concatenate
")

Hope this helps.

Bill Horton

"cstwoplus" wrote:

I have a list of contacts with email addresses. My client is changing
their
email layout from to . I would
like to
replace the jmsith with john.smith, which I can get by concatenating
the
first and last name columns with a dot, but how do I get it to replace
everything up to the @ with the newly concatenated name? I tried a
combination of search, replace, etc, but I couldn't get the combo
right. Any
help is appreciated.
Everything after the @ remains the same.

Thanks,
cstwoplus

Do you have the list of new names in your worksheet in a column?

Say column D with your current addresses in Column A

Split out the domain names into column B using DataText to columns delimited by
other. Enter the @ symbol and Finish

Then in an adjacent column enter =D1 & "@" & B1


Gord Dibben MS Excel MVP


On Wed, 11 Apr 2007 08:06:04 -0700, cstwoplus
wrote:

I did not make it clear that the list I need to change has 400 names, and
they do not all end with @client.com. They all have the same layout, but the
domain name changes, so I need to keep that intact while removing everything
before the @ and replacing it with the concatenated name.
becomes
becomes
becomes

Sorry for not being clear.

cstwoplus

"William Horton" wrote:

CONCATENATE("FirstName", ".", "LastName", ")

If you have the entire first and last names in separate fields, you can do
this all with one concatenate function. (Concatenate
")

Hope this helps.

Bill Horton

"cstwoplus" wrote:

I have a list of contacts with email addresses. My client is changing their
email layout from to . I would like to
replace the jmsith with john.smith, which I can get by concatenating the
first and last name columns with a dot, but how do I get it to replace
everything up to the @ with the newly concatenated name? I tried a
combination of search, replace, etc, but I couldn't get the combo right. Any
help is appreciated.
Everything after the @ remains the same.

Thanks,
cstwoplus

That is fantastic! Works like a charm. Thank you very much.

"David Biddulph" wrote:

="first name"&"."&"last name"&RIGHT(A1,LEN(A1)+1-FIND("@",A1))
--
David Biddulph

"cstwoplus" wrote in message
...
I did not make it clear that the list I need to change has 400 names, and
they do not all end with @client.com. They all have the same layout, but
the
domain name changes, so I need to keep that intact while removing
everything
before the @ and replacing it with the concatenated name.
becomes
becomes
becomes

Sorry for not being clear.

cstwoplus

"William Horton" wrote:

CONCATENATE("FirstName", ".", "LastName", ")

If you have the entire first and last names in separate fields, you can
do
this all with one concatenate function. (Concatenate
")

Hope this helps.

Bill Horton

"cstwoplus" wrote:

I have a list of contacts with email addresses. My client is changing
their
email layout from to . I would
like to
replace the jmsith with john.smith, which I can get by concatenating
the
first and last name columns with a dot, but how do I get it to replace
everything up to the @ with the newly concatenated name? I tried a
combination of search, replace, etc, but I couldn't get the combo
right. Any
help is appreciated.
Everything after the @ remains the same.

Thanks,
cstwoplus