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

philky001

Board Regular
Joined
Jun 8, 2005
Messages
111
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:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,420
Assuming that the first name is in column A and last name is in column B: =B1&"."&A1&"@dcf.nj.gov"
 

philky001

Board Regular
Joined
Jun 8, 2005
Messages
111
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
Joined
Apr 11, 2012
Messages
8,420
Try: =LEFT(A1,FIND(",",A1,1)-1)&"."&MID(A1,FIND(",",A1,1)+1,9999)&"@dcf.nj.gov"
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,113
Office Version
365
Platform
Windows
Another option
=SUBSTITUTE(SUBSTITUTE(A1,",",".")," ","")&"@dcf.nj.gov"
 

philky001

Board Regular
Joined
Jun 8, 2005
Messages
111
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
Joined
Jun 8, 2005
Messages
111
the substitute did create but ee have to flip the names as well.
 
Last edited:

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,420
Try: =MID(A1,FIND(",",A1,1)+1,9999)&"."&LEFT(A1,FIND(",",A1,1)-1)&"@dcf.nj.gov"
 

Forum statistics

Threads
1,077,641
Messages
5,335,411
Members
399,014
Latest member
emalabel

Some videos you may like

This Week's Hot Topics

Top