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:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
8,755
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,755
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
35,883
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,755
Try: =MID(A1,FIND(",",A1,1)+1,9999)&"."&LEFT(A1,FIND(",",A1,1)-1)&"@dcf.nj.gov"
 

Watch MrExcel Video

Forum statistics

Threads
1,090,000
Messages
5,411,757
Members
403,395
Latest member
jg33

This Week's Hot Topics

Top