SOLVED

New Contributor

# first and last name to email from one cell

Hi,

I am trying to create a code to have the first and last name from a single cell to an email address with a dott in between the first and the last name. Example, Anton van der sloot --> anton.vandersloot@...

Another example,

piet de herder --> piet.deherder@......

Does somebody know the code for this?

4 Replies
best response confirmed by Philippevdlof (New Contributor)
Solution

# Re: first and last name to email from one cell

``=LEFT(C5,SEARCH(" ",C5)-1)&"."&SUBSTITUTE(RIGHT(C5,LEN(C5)-SEARCH(" ",C5))," ","")&"@email.com"``

Maybe with this formula.

# Re: first and last name to email from one cell

Thank you very much! It works.

# Re: first and last name to email from one cell

@Quadruple_Pawn Is there a way to erase #value! message for the cells that aren't filled out?

# Re: first and last name to email from one cell

``=IFERROR(LEFT(C5,SEARCH(" ",C5)-1)&"."&SUBSTITUTE(RIGHT(C5,LEN(C5)-SEARCH(" ",C5))," ","")&"@email.com","")``

You can wrap the IFERROR around the formula.