Function/VBA code to extract first & last name from email addresses

[Shak1701]

I have a list of email address, in the format, 'Firstname.Lastname@Companyname.com'. My issue arises where multiple managers are serving the same function, these cells contain more than one email address in the aforementioned format, split by a ' ; '. Eg 'Firstname.Lastname@Companyname.com; 'Firstname.Lastname@Companyname.com ...'

Now I am only interested in extracting the first and last name of these managers, whether that be a single email address or a cell contains multiple. I understand the VBA function SPLIT will work, but I am unsure on how to code it.

Copy of Current Employees 09.11.2021.xlsm
	L
998	melane.aespray@arcusgroup.co.uk; Evy.Willinson@arcusfm.com
999	melane.aespray@arcusgroup.co.uk; Evy.Willinson@arcusfm.com
1000	melane.aespray@arcusgroup.co.uk; Evy.Willinson@arcusfm.com
1001	melanie.aespray@arcusgroup.co.uk; Evy.Willinson@arcusfm.com
1002	iscm.sefield@arcusgroup.co.uk
1003	iscm.sefield@arcusgroup.co.uk; iscm.guisborough@arcusgroup.co.uk; ISCM.Yarm@arcusgroup.co.uk
1004	iscm.sedgefield@arcus-group.co.uk; iscm.guisborough@arcus-group.co.uk; ISCM.Yarm@arcus-group.co.uk
Sheet1

Cell Formulas
Range	Formula
L998:L1004	L998	=RemoveDupeWords(K998,"; ")

I already have VBA code to remove duplicates.

Thank you in advance.

 

[JEC]

Try this one

Function jec(cell As String) As String
  Dim a As Variant, i As Long
  a = Split(cell, ";")
  
  For i = 0 To UBound(a)
    jec = jec & IIf(jec = "", "", ",") & Split(a(i), "@")(0)
  Next
End Function

 

[Shak1701]

Try this one

Function jec(cell As String) As String
  Dim a As Variant, i As Long
  a = Split(cell, ";")
 
  For i = 0 To UBound(a)
    jec = jec & IIf(jec = "", "", ",") & Split(a(i), "@")(0)
  Next
End Function

Thank you SO much JEC. I only have a slight issue now, how do I remove the '.' between the names?

 

[JEC]

You're welcome!

Like this:

Function jec(cell As String) As String
  Dim a As Variant, i As Long
  a = Split(cell, ";")
  
  For i = 0 To UBound(a)
    jec = jec & IIf(jec = "", "", ",") & Replace(Split(a(i), "@")(0), ".", " ")
  Next
End Function