Excel Excel seperate first & last name

F

fuzool789

Joined
Jan 20, 2013
Messages
1
Reaction score
0
Hello,

I have an Excel assignment about seperating First & Last names in a given sheet. The sheet contains names as follows:
Forlani, Claire
Trejo, Danny
Prinze, Freddie Jr.
Mulroney, Dermot
Charles, Josh (SWAT)
Charles, Josh (SWAT)
Blair, Selma
Super Troopers
Broken Arrow (John Travolta / Christian Slater)
Goodfellas (Ray Liotta / Lorraine Bracco)

I am looking for a formula that will make the above names to:
Claire Forlani
Danny Trejo
Freddie Jr. Prinze
Dermot Mulroney
Josh Charles (SWAT)
Super Troopers
Broken Arrow (John Travolta / Christian Slater)
Goodfellas (Ray Liotta / Lorraine Bracco)

The only catch is Charles, Josh (SWAT) and I only want it to read Josh Charles (SWAT). The last three will remain the same.
Let me know if someone can write a formula for this ?
 
Here you go, one and only answer!
Assuming your data is in A1 & copy down.

=IF(AND(ISNUMBER(SEARCH("(",A1)),ISNUMBER(SEARCH(",",A1))),MID(A1,FIND(" ",A1)+1,FIND(" (",A1)-1-FIND(" ",A1))&" "&LEFT(A1,FIND(",",A1)-1)&" "&TRIM(RIGHT(SUBSTITUTE(A1," ",REPT(" ",100)),100)),IF(ISNUMBER(SEARCH(",",A1)),RIGHT(A1,LEN(A1)-FIND(",",A1)-1)&" "&LEFT(A1,FIND(",",A1)-1),A1))
 
Back
Top