I need to add multiple formulas to convert firstname.lastname@email.com into fl@email.com

[jiuanchoja]

Hello all,

I am trying to take an email from one cell, say for example A1 and I want to do a few things, the first one is to take the first initials of the first and last name, written as first.last@email.com.

I want to convert this into fl@email.com

What I have done is to use the following formulas. They work well in different cells, but I can't put them together into the same cell. What my formula can do so far and whats on each cell:

in cell A1: First Last
in cell B1: job title
in cell C1: first.last@email.com
in cell F1: =LEFT(A1)&IF(ISNUMBER(FIND(" ",A1)),MID(A1,FIND(" ",A1)+1,1),"")&IF(ISNUMBER(FIND(" ",A1,FIND(" ",A1)+1)),MID(A1,FIND(" ",A1,FIND(" ",A1)+1)+1,1),"")
in cell G1: =CONCATENATE(F1, "@email.com ")

What I want to do is to put both formulas in F1 and G1 in F1 to return fn@email.com

I have the same problem when I want to convert from first.last@email.com into f.l@email.com using first initials and adding a "." My formula in two cells which work is

in cell A1: First Last
in cell B1: job title
in cell C1: first.last@email.com
in cell D1: =FirstCharacters(A1)
in cell E1: =REPLACE(D1, 4, 1,"@email.com")

I also need a formula to convert from first.last@email.com into firstlast@email.com (taking out the dot ) and viceversa.

Thank you for your advice.

 

[FormR]

Hi, welcome to the forum!

Does this cover all the variations?

Excel 2013
	C	D	E	F
1	first.last@email.com	fl@email.com	f.l@email.com	firstlast@emailcom
Sheet1

Cell Formulas
Range	Formula
D1	=LEFT(C1)&MID(C1,FIND(".",C1)+1,1)&"@email.com"
E1	=LEFT(C1)&MID(C1,FIND(".",C1),2)&"@email.com"
F1	=SUBSTITUTE(C1,".","")

 

[FormR]

EDIT: The formula in F1 should be changed to:

=SUBSTITUTE(C1,".","",1)