Formula to join two cells with a dot in between?

[Mr2017]

Hi

I have a list of names where I'd like to combine the First Name and Surname (but have a dot in between them), then add a corporation at the end of each name so that their email address is created automatically!

I'm sure this is possible, but I can't remember how to do it!

Please find below some sample data.

Cells A1 and A2 have the names John Smith and Jane Doe, respectively.

B1 and B2 have lower case versions of the data in cells A1 and A2, using the =lower(A1) and =lower(A2) formulas, respectively.

C1 and C2 are cells where I'd like to return the e-mail addresses.

And D1 and D2 have the '@' sign and the corporation that I'd like to add to the end of the names.

Does anyone know how to do this, please? I can add the corporation name myself. It's the joining of the names (which will vary in length eg Johnny Smith instead of John Smith) that I can't remember the formula for!

John Smith	john smith		@microsoft.com
Jane Doe	jane doe		@microsoft.com

 

[jmacleary]

Hi there. Try this, but be aware that, as I have shown on the third line, every blank will be replaced by a period in the output.

Book1.xlsm
	A	B	C	D
1	John Smith	john smith	john.smith@microsoft.com	@microsoft.com
2	Jane Doe	jane doe	jane.doe@microsoft.com	@microsoft.com
3	Mr Fred Bloggs	mr fred bloggs	mr.fred.bloggs@microsoft.com	@microsoft.com
Sheet2

Cell Formulas
Range	Formula
B1:B3	B1	=LOWER(A1)
C1:C3	C1	=SUBSTITUTE(B1," ",".")&D1

 

[Mr2017]

Awesome!

Thanks for the prompt response!

That worked - thank you!

 

[jmacleary]

You're welcome and thanks for the feedback.

 

[Mr2017]

No problem.

Thank you!