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

[philky001]

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

 

[mumps]

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

 

[philky001]

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]

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

 

[Fluff]

Another option
=SUBSTITUTE(SUBSTITUTE(A1,",",".")," ","")&"@dcf.nj.gov"

 

[philky001]

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]

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

 

[mumps]

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