Convert proper name to email address

[Liverlee]

Hi All

I'm trying to convert a list of proper names in column B CELL 2 through to 2000.

John Doe
Jane Doe
John Smith

To john.doe@email.name.

I can use concatenate =CONCATENATE(B2,"@","emailname") to get john doe@emailname.com

How do i get rid of the space between first and last name and replace it with a full stop, i don't really want to split the cell to first and last names.

Can i nest a formula SUBSTITUE, TRIM FIND CONCATENATE?

Or is there an easy method that's alluding me?

Any help appreciated

 

[FormR]

Can i nest a formula SUBSTITUE

Hi, yes you can. Something like:

=CONCATENATE(SUBSTITUTE(B2," ","."),"@","emailname")

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C
1
2		John Doe	John.Doe@emailname.com
3		Jane Doe	Jane.Doe@emailname.com
4		John Smith	John.Smith@emailname.com
Data

Cell Formulas
Range	Formula
C2:C4	C2	=SUBSTITUTE(B2," ",".")&"@emailname.com"

 

[Liverlee]

Brilliant, thanks both, the formula works a treat - i've been googling round for ages, and it two minutes you've solved my problem, thanks again.

 

[Fluff]

Glad we could help & thanks for the feedback.