Get Last names from on multiple full names

[nikhil0311]

Hello Friends, looking for some advanced excel formula help. I want to get the last name only based on multiple full names. There can be any number of full names in a single cell and for those i want the last names in column B (per below example). Please let me know if any excel formula can achieve this output.

last name based on multiple full names.xlsx
	A	B	C
1	Sr no	Full name	Output - Last name
2	1	Arteta, Mikel; Trossard, Leandro N; Nelson, Robin T	Arteta, Trossard, Nelson
3	2	Ramsey, Aaron N; Hazard, Eden M	Ramsey, Hazard
4	3	Wenger, A W	Wenger
Sheet1

 

[Peter_SSs]

If you are still using Excel 2013 as shown in your profile, doing this with worksheet formulas would not be easy.
Instead, you could consider the user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

Function LastNames(s As String) As String
  Dim itm As Variant
  
  For Each itm In Split(s, ";")
    LastNames = LastNames & ", " & Trim(Split(itm, ",")(0))
  Next itm
  LastNames = Mid(LastNames, 3)
End Function

nikhil0311.xlsm
	B	C
1	Full name	Output - Last name
2	Arteta, Mikel; Trossard, Leandro N; Nelson, Robin T	Arteta, Trossard, Nelson
3	Ramsey, Aaron N; Hazard, Eden M	Ramsey, Hazard
4	Wenger, A W	Wenger
5	Hall-Oates, Tom	Hall-Oates
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=LastNames(B2)

 

[nikhil0311]

If you are still using Excel 2013 as shown in your profile, doing this with worksheet formulas would not be easy.
Instead, you could consider the user-defined function. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot below and copy down.

Function LastNames(s As String) As String
  Dim itm As Variant
 
  For Each itm In Split(s, ";")
    LastNames = LastNames & ", " & Trim(Split(itm, ",")(0))
  Next itm
  LastNames = Mid(LastNames, 3)
End Function

nikhil0311.xlsm
	B	C
1	Full name	Output - Last name
2	Arteta, Mikel; Trossard, Leandro N; Nelson, Robin T	Arteta, Trossard, Nelson
3	Ramsey, Aaron N; Hazard, Eden M	Ramsey, Hazard
4	Wenger, A W	Wenger
5	Hall-Oates, Tom	Hall-Oates
Sheet1

Cell Formulas
Range	Formula
C2:C5	C2	=LastNames(B2)

This is great Mr. Peter. Thanks a lot for a quick turn around. One minor change, how do i replace "," with ";" in the final output?

 

[Peter_SSs]

One minor change, how do i replace "," with ";" in the final output?

Function LastNames(s As String) As String
  Dim itm As Variant
 
  For Each itm In Split(s, ";")
    LastNames = LastNames & "; " & Trim(Split(itm, ",")(0))
  Next itm
  LastNames = Mid(LastNames, 3)
End Function