Extracting Names w Formula Many Different Characters

mgirvin

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

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

Forum statistics

Threads
1,082,323
Messages
5,364,580
Members
400,809
Latest member
formulasataglance

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top