Extracting Names w Formula Many Different Characters

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

bs0d

Well-known Member
Joined
Dec 29, 2006
Messages
622
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

Well-known Member
Joined
Mar 27, 2006
Messages
1,175
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,099
Messages
5,570,207
Members
412,308
Latest member
jt145
Top