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,444
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,444
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,555
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,444
Try: =MID(A1,FIND(",",A1,1)+1,9999)&"."&LEFT(A1,FIND(",",A1,1)-1)&"@dcf.nj.gov"
 

Forum statistics

Threads
1,078,486
Messages
5,340,620
Members
399,387
Latest member
amrita34

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top