Create macro to separate first name and last name in email and plug it into columns

[Jyggalag]

Dear all,

I am currently working on a spreadsheet that looks like this, albeit the real one is much larger:

I was wondering if there would be some way in which I could create a macro or some VBA code attached to a macro where, once i press it, it takes the e-mails from C2-C12 and takes the first name and puts it in column A and the last name in column B. Almost all of the emails that I work with have emails that basically follow a rule of "Firstname.Lastname@email.com". If they do not have this first name separated from the last name by a dot ".", I would like for the macro to return the value "X" and "X" for both first name and last name in column A and B.

Is this possible? And if so, how might I carry out such a task?

Thank you so much for your time and assistance everybody! It is greatly appreciated.

Please let me know if you have questions or need further information and I will respond ASAP.

Kindest regards,
Jyggalag.

 

[eastrand]

Here's one solution. I also through the proper case in just in case not all the emails are proper case already.

Book1
	A	B	C
1	First	Last	email
2	Bob	Igor	bob.igor@xyc.com
3	Natalia	Henriksen	natalia.henriksen@pharmatest.com
4	George	Washington	george.washington@senator.com
5	Donald	Harris	donald.harris@privateequity.com
6	X	X	something@gmail.com
7	X	X	iaminoneword@badcorp.com
8	Steve	Wallis	steve.wallis@entpres.com
Sheet1

Cell Formulas
Range	Formula
A2:A8	A2	=IFERROR(PROPER(LEFT(LEFT($C2,FIND("@",$C2)-1),FIND(".",LEFT($C2,FIND("@",$C2)-1))-1)),"X")
B2:B8	B2	=IFERROR(PROPER(RIGHT(LEFT($C2,FIND("@",$C2)-1),LEN(LEFT($C2,FIND("@",$C2)-1))-FIND(".",LEFT($C2,FIND("@",$C2)-1)))),"X")

 

[Fluff]

Another formula option

+Fluff 1.xlsm
	A	B	C
1	First	Last	email
2	Bob	Igor	bob.igor@xyc.com
3	Natalia	Henriksen	natalia.henriksen@pharmatest.com
4	George	Washington	george.washington@senator.com
5	Donald	Harris	donald.harris@privateequity.com
6	X	X	something@gmail.com
7	X	X	iaminoneword@badcorp.com
8	Steve	Wallis	steve.wallis@entpres.com
Data

Cell Formulas
Range	Formula
A2:A8	A2	=IF(FIND(".",C2)>FIND("@",C2),"X",PROPER(LEFT(C2,FIND(".",C2)-1)))
B2:B8	B2	=IF(FIND(".",C2)>FIND("@",C2),"X",PROPER(MID(REPLACE(C2,FIND("@",C2),200,""),FIND(".",C2)+1,100)))

 

[Fluff]

Or with a macro

Sub Jyggalag()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Offset(, -2).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),""X"",PROPER(LEFT(#,FIND(""."",#)-1)))", "#", .Address))
      .Offset(, -1).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),""X"",PROPER(MID(REPLACE(#,FIND(""@"",#),200,""""),FIND(""."",#)+1,100)))", "#", .Address))
   End With
End Sub

 

[Jyggalag]

Another formula option

+Fluff 1.xlsm
	A	B	C
1	First	Last	email
2	Bob	Igor	bob.igor@xyc.com
3	Natalia	Henriksen	natalia.henriksen@pharmatest.com
4	George	Washington	george.washington@senator.com
5	Donald	Harris	donald.harris@privateequity.com
6	X	X	something@gmail.com
7	X	X	iaminoneword@badcorp.com
8	Steve	Wallis	steve.wallis@entpres.com
Data

Cell Formulas
Range	Formula
A2:A8	A2	=IF(FIND(".",C2)>FIND("@",C2),"X",PROPER(LEFT(C2,FIND(".",C2)-1)))
B2:B8	B2	=IF(FIND(".",C2)>FIND("@",C2),"X",PROPER(MID(REPLACE(C2,FIND("@",C2),200,""),FIND(".",C2)+1,100)))

This is amazing Fluff, thank you so much!!

Would there be a way change to the last name formula so it returns the entire value that is written before the @ sign?

Otherwise truly amazing to see that you're active here with your great solutions! Truly appreciated

 

[Jyggalag]

Or with a macro

Sub Jyggalag()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Offset(, -2).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),""X"",PROPER(LEFT(#,FIND(""."",#)-1)))", "#", .Address))
      .Offset(, -1).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),""X"",PROPER(MID(REPLACE(#,FIND(""@"",#),200,""""),FIND(""."",#)+1,100)))", "#", .Address))
   End With
End Sub

Also same question for the macro. It would be nice if it, instead of printing "X" for first name could print "Team" and instead of "X" for last name, could print whatever is written before the @ sign in the email. So for example value C9 would become First name: Team, Last name: Something

Apologies for the confusion and thank you so much!

 

[Fluff]

Do you want this as a macro or formula?

 

[Jyggalag]

Do you want this as a macro or formula?

Preferably macro, but if you have the luxury of doing both (so I can keep an alternative solution at hand), it would be incredibly appreciated from my point of view

Thank you so much for your time once again!

 

[Fluff]

Ok, how about

Sub Jyggalag()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Offset(, -2).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),""Team"",PROPER(LEFT(#,FIND(""."",#)-1)))", "#", .Address))
      .Offset(, -1).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),left(#,find(""@"",#)-1),PROPER(MID(REPLACE(#,FIND(""@"",#),200,""""),FIND(""."",#)+1,100)))", "#", .Address))
   End With
End Sub

 

[Jyggalag]

Ok, how about

Sub Jyggalag()
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Offset(, -2).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),""Team"",PROPER(LEFT(#,FIND(""."",#)-1)))", "#", .Address))
      .Offset(, -1).Value = Evaluate(Replace("IF(FIND(""."",#)>FIND(""@"",#),left(#,find(""@"",#)-1),PROPER(MID(REPLACE(#,FIND(""@"",#),200,""""),FIND(""."",#)+1,100)))", "#", .Address))
   End With
End Sub

Hm I tried inserting it but I get this message now:

In my module the code looks like this:

Did I perhaps enter something in an incorrect way?

Apologies for the confusion!

Kind regards,
Jyggalag