updating email addresses in cells

[Guest]

I have a list of contacts with email addresses. My client is changing their
email layout from (e-mail address removed) to (e-mail address removed). 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

 

[Guest]

CONCATENATE("FirstName", ".", "LastName", "@client.com")

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

Hope this helps.

Bill Horton

 

[Guest]

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.
(e-mail address removed) becomes (e-mail address removed)
(e-mail address removed) becomes (e-mail address removed)
(e-mail address removed) becomes (e-mail address removed)

Sorry for not being clear.

cstwoplus

 

[David Biddulph]

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

 

[Gord Dibben]

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 Data>Text to columns delimited by
other. Enter the @ symbol and Finish

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


Gord Dibben MS Excel MVP

 

[Guest]

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