Extracting Names w Formula Many Different Characters

[mgirvin]

In the range A2:A5 I have the names:

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.

 

[bs0d]

You could consider normalizing the data and convert them all to one format (use find and replace). Then that would reduce the complexity of your formulas. If you can have a special character between the key parts of the name, then it should be much easier to extract what you want. You could even do a txt-to-cols and have the data in 3 columns...

 

[West Man]

A single formula would be great, but there are so many posibilities in the data that it is unlikely one can be developed. I have found that an approach of classifying the data and handling each group seperately is about the only practical method. You can classify in several ways

Count the interior spaces to see which have a middle name/initial and/or suffix, determine if a period exists in the name, determine if a dash exists, etc.

Sort by the classification and handle each seperately