Extracting Names w Formula Many Different Characters


Well-known Member
Dec 15, 2005
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


Well-known Member
Dec 29, 2006
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
Mar 27, 2006
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

Latest member