Split a name string into first name , middle initial and last name

**Lady_Shaz** · 01-23-2013, 08:10 PM

I want to read in a name string that may be in several formats and from that extract the first name, middle initial and last name.
Please see attached file for possible formats in the name column. Fields highlighted in yellow are the results I want to achieve through the formula.
Thanks
ShazName Formula.xls

**benishiryo** · 01-23-2013, 08:39 PM

hi Lady_Shaz, try this in B2 copied down & across:
=TRIM(SUBSTITUTE(MID(SUBSTITUTE(TRIM($A2)," ",REPT(" ",50)),1+(COLUMNS($B2:B2)-1)*50,50),",",""))

**Lady_Shaz** · 01-23-2013, 08:51 PM

Hi benishiryo, thanks heaps that works however I have come across another problem.
I am working with the data now and have found different name formats.
Please see attached file with the new formats highlighted in pink.
Cheers
Shaz
Name Formula.xls

**benishiryo** · 01-24-2013, 01:34 AM

try this for the last name:
=IF(ISNUMBER(SEARCH("(*)",A2)),REPLACE(A2,SEARCH("(*)",A2),100,""),LEFT(A2,FIND(",",A2)-1))
then subsequently:
=TRIM(SUBSTITUTE(MID(SUBSTITUTE(TRIM(REPLACE($A2,1,FIND(",",$A2),""))," ",REPT(" ",50)),1+(COLUMNS($B2:B2)-1)*50,50),",",""))