mgirvin
Well-known Member
- Joined
- Dec 15, 2005
- Messages
- 1,236
- Office Version
- 365
- Platform
- Windows
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.
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.