Extract First Name and Middle Name from 2 or 3 part last name, first name

[fvo2715]

Hi;

I have a list of Name formatted as LAST, FIRST MIDDLE
Ex: (I able to find 1 word Last name to extract the first name and last name) but below:

DE LA FUENTE, OCHOA J. (How to extract First name: Ochoa and Middle Name: J.

or

Lopez Valenzuela, Mary Jan (how to extract First Name: Mary & Middle Name: Jan)

Please help with a formula without VBA.

Thank You

 

[azumi]

Hope this helps

 

[bosco_yip]

Try this formula solution without VBA..............



To extract First and Middle name,

In B2, copied across to C2 and all copied down :

=TRIM(MID(SUBSTITUTE(MID($A2,FIND(",",$A2)+1,99)," ",REPT(" ",50)),COLUMNS($A:A)*50,50))

Regards
Bosco