Mike J Hunter

Norman-Price Smith

Harry. Redknapp

Prince-H, Hughes

My goal is to extract First, Middle and Last names to get this:

Mike -------------- J ----------- Hunter

Norman ----------- Price ------- Smith

Harry ------------- ------------ Redknapp

Prince ------------ H ----------- Hughes

(the dashes are just to separate the three elements)

The complication for me is that I could not see an easy pattern to exploit in order to extract the names with a formula because some names have spaces, some have dashes, some have commas and there are extraneous periods also. I eventually came up with these formulas:

First Name:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""),FIND("" "",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""))-1)

Middle Name:

=LEFT(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""),1,FIND("" "",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" "")),""""),FIND("" "",REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""),1,FIND("" "",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" "")),""""))-1)

Last Name:

=TRIM(RIGHT(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""),1,FIND("" "",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" "")),""""),LEN(REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""),1,FIND("" "",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" "")),""""))-FIND("" "",REPLACE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" ""),1,FIND("" "",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,""-"","" ""),""."","" ""),"","","" "")),""""))))"

Anyone have any easier formulas?

I Tried Text to Column, but I had to visually track down too many anomalies such as periods and situations where there was no middle name and so I was hoping for a formula that would do it.