# Create an email address for all rows that don't have it

#### philky001

##### Board Regular
I have an exported excel file from MS ACCESS that is fine but some don't have email addresses, But they are all the same that is, Lastname. (put in a '.')Firstname@dcf.nj.gov

How can I make a formula that will do this?
Data

Lastname Firstname email
Smit Joe currently blank
Smit Jane test.Jane@dcfrf.ny.gov

Last edited:

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### mumps

##### Well-known Member
Assuming that the first name is in column A and last name is in column B: =B1&"."&A1&"@dcf.nj.gov"

#### philky001

##### Board Regular
Assuming that the first name is in column A and last name is in column B: =B1&"."&A1&"@dcf.nj.gov"
Thanks, I am sorry I made a mistake earlier
the name is stored in one col. as
Lastname,firstname

#### mumps

##### Well-known Member
Try: =LEFT(A1,FIND(",",A1,1)-1)&"."&MID(A1,FIND(",",A1,1)+1,9999)&"@dcf.nj.gov"

#### Fluff

##### MrExcel MVP, Moderator
Another option
=SUBSTITUTE(SUBSTITUTE(A1,",",".")," ","")&"@dcf.nj.gov"

#### philky001

##### Board Regular
Another problem, is the email flips the order. The data in col a is lastname,firstname. but the email address will be firstname.lastname

#### philky001

##### Board Regular
the substitute did create but ee have to flip the names as well.

Last edited:

#### mumps

##### Well-known Member
Try: =MID(A1,FIND(",",A1,1)+1,9999)&"."&LEFT(A1,FIND(",",A1,1)-1)&"@dcf.nj.gov"