Splitting out title, first name, middle name, last name

[OP193]

Hi all

I know there's been multiple posts on this throughout the past but all with slightly different requirements...

I have a list of c.1500 names (some with middle names, some without) and i'm trying to split them out by title, first, middle (if there is one) and surname. I've tried text to columns but obviously as not all have middle names it doesn't give the result I want.

So in cell E5 i have "Mr Joe x Bloggs"

What I'm trying to achieve is

F5: Mr
G5: Joe
H5: x
I5: Bloggs

Where there isn't a middle name it would be great if i could automatically pull the surname into I ignoring H, but worst case scenario i can filter by blanks on I and pull across the surnames sat in H.


Any help much appreciated..

Thanks

 

[MPaul100]

See if this helps: https://www.exceltip.com/excel-text/extract-the-first-middle-and-last-name-in-microsoft-excel.html

 

[tyija1995]

Hey try this:

F5:
TRIM(MID(SUBSTITUTE($E5,CHAR(32),REPT(CHAR(32),LEN($E5))),(COLUMN()-6)*LEN($E5)+1,LEN($E5)))

Drag across the row

 

[SupremeDr]

Do all names have a title? Are middle names just middle initials or the full name?

Are the names formatted in a way where you can text to columns based on column width?

 

[kweaver]

If all the names have a prefix and there are no suffix abbreviations on any name AND if everything is separated by a space (phew), I think this (UGLY) formula set works:

Book1
	E	F	G	H	I
4		Prefix	First	Middle	Last
5	Mr. Joe X Boggs	Mr.	Joe	X	Boggs
6	Mr. William Mark Smith	Mr.	William	Mark	Smith
7	Mr. Samuel Oscar Smithe	Mr.	Samuel	Oscar	Smithe
8	Miss Susan Jones	Miss	Susan		Jones
9	Miss Mary Kay Johnson	Miss	Mary	Kay	Johnson
10	Dr. Master Surgeon	Dr.	Master		Surgeon
Sheet1

Cell Formulas
Range	Formula
F5	=LEFT(E5,FIND(" ",E5)-1)
G5	=MID(E5,1+FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),1)),(FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),2)))-FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),1)))
H5	=IF(3=SUMPRODUCT(LEN(E5)-LEN(SUBSTITUTE(E5," ",""))),MID(E5,FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),2))+1,(FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),3)))-FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),2))),"")
I5	=RIGHT(E5,LEN(E5)-FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),LEN(E5)-LEN(SUBSTITUTE(E5," ",""))),1))

 

[Fluff]

What happens if you have a name like
Mr Joost van der Westhuizen

 

[Rick Rothstein]

What happens if you have a name like
Mr Joost van der Westhuizen

Or his wife...

Mary Ann van der Westhuizen

where Mary Ann is the full, legal first name (I worked with someone with that first name). The point here is you cannot assume all first and last names are single names.

 

[kweaver]

That's a problem! GRIN

I clearly made some assumptions based on the OP's post and my own thinking.

This type of problem is very tricky. There could be an ESQ, III, Sr., Jr., type of suffix at the end of the name as well.

 

[Fluff]

Not forgetting titles such as
The Right Honorable
or
The Very Reverend

 

[OP193]

Thanks all for the quick replies! I’ll test these solutions in the morning and come back to you ?