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

 

[salim hasan]

You can use my UDF function

Option Explicit


Function Salim_Split_Name(N_name, n)
Rem  ====>> Created By Salim Hasan On 13/8/2019
Dim x%
Dim my_name
Dim My_Col As New Collection
 
  my_name = Split(N_name)
   For x = LBound(my_name) To UBound(my_name)
        My_Col.Add my_name(x)
   Next x
    If n <= My_Col.Count Then
        Salim_Split_Name = My_Col(n)
    Else
        Salim_Split_Name = ""
    End If
     Set My_Col = Nothing
  End Function

	E	F	G	H	I	J
Mr Joe x Bloggs	Mr	Joe	x	Bloggs
Mr Adam Tom Jerry Mouse	Mr	Adam	Tom	Jerry	Mouse

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=IF($E5="","",Salim_Split_Name($E5,COLUMNS($F$5:F5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=IF($E5="","",Salim_Split_Name($E5,COLUMNS($F$5:G5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=IF($E5="","",Salim_Split_Name($E5,COLUMNS($F$5:H5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=IF($E5="","",Salim_Split_Name($E5,COLUMNS($F$5:I5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J5[/TH]
[TD="align: left"]=IF($E5="","",Salim_Split_Name($E5,COLUMNS($F$5:J5)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F6[/TH]
[TD="align: left"]=IF($E6="","",Salim_Split_Name($E6,COLUMNS($F$5:F6)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF($E6="","",Salim_Split_Name($E6,COLUMNS($F$5:G6)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H6[/TH]
[TD="align: left"]=IF($E6="","",Salim_Split_Name($E6,COLUMNS($F$5:H6)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I6[/TH]
[TD="align: left"]=IF($E6="","",Salim_Split_Name($E6,COLUMNS($F$5:I6)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J6[/TH]
[TD="align: left"]=IF($E6="","",Salim_Split_Name($E6,COLUMNS($F$5:J6)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

You can use my UDF function

Function Salim_Split_Name(N_name, n)
Rem  ====>> Created By Salim Hasan On 13/8/2019
Dim x%
Dim my_name
Dim My_Col As New Collection
 
  my_name = Split(N_name)
   For x = LBound(my_name) To UBound(my_name)
        My_Col.Add my_name(x)
   Next x
    If n <= My_Col.Count Then
        Salim_Split_Name = My_Col(n)
    Else
        Salim_Split_Name = ""
    End If
     Set My_Col = Nothing
End Function

While your UDF does not address what Fluff, kweaver and myself pointed out, I do note that your UDF can be simplified to this...

Function Salim_Split_Name(N_name, N)
  On Error GoTo BadN
  Salim_Split_Name = Split(N_name)(N - 1)
BadN:
End Function

 

[OP193]

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:

	E	F	G	H	I
Prefix	First	Middle	Last
Mr. Joe X Boggs	Mr.	Joe	X	Boggs
Mr. William Mark Smith	Mr.	William	Mark	Smith
Mr. Samuel Oscar Smithe	Mr.	Samuel	Oscar	Smithe
Miss Susan Jones	Miss	Susan		Jones
Miss Mary Kay Johnson	Miss	Mary	Kay	Johnson
Dr. Master Surgeon	Dr.	Master		Surgeon

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]

[TD="align: center"]10[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F5[/TH]
[TD="align: left"]=LEFT(E5,FIND(" ",E5)-1)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G5[/TH]
[TD="align: left"]=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)))[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]H5[/TH]
[TD="align: left"]=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))),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I5[/TH]
[TD="align: left"]=RIGHT(E5,LEN(E5)-FIND(CHAR(160),SUBSTITUTE(E5," ",CHAR(160),LEN(E5)-LEN(SUBSTITUTE(E5," ",""))),1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Brilliant. This worked perfectly thank you! There's a few right honourables but i can just pull them out manually no problem! Thank you all again

 

[Peter_SSs]

Brilliant. This worked perfectly thank you! There's a few right honourables but i can just pull them out manually no problem! Thank you all again

If you are happy to deal with the few miscreants that kweaver's formula might leave, then here are some considerably more compact formulas that I think should still do the same job.

Excel Workbook
	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
Extract Names

 

[kweaver]

Nicely done, Peter! (of course)

 

[Peter_SSs]

Nicely done, Peter! (of course)

Cheers.

Just playing with the OP's Text to Columns idea a bit more & it could be partially utilised as follows (though I'm not suggesting this as the best option)

1. Formula in F2 copied down
2. Copy column F and paste Values (I've done it in the next column but it could b over the top of either E or F)
3. On those pasted values, Text to Columns -> Delimited -> Space -> Uncheck 'Treat consecutive delimiters as one' -> Finish (though I first did 'Next' and chose a new Destination first so the original data was still visible)

Excel Workbook
	E	F	G	H	I	J	K
5	Mr. Joe X Boggs	Mr. Joe X Boggs	Mr. Joe X Boggs	Mr.	Joe	X	Boggs
6	Mr. William Mark Smith	Mr. William Mark Smith	Mr. William Mark Smith	Mr.	William	Mark	Smith
7	Mr. Samuel Oscar Smithe	Mr. Samuel Oscar Smithe	Mr. Samuel Oscar Smithe	Mr.	Samuel	Oscar	Smithe
8	Miss Susan Jones	Miss SusanJones	Miss SusanJones	Miss	Susan		Jones
9	Miss Mary Kay Johnson	Miss Mary Kay Johnson	Miss Mary Kay Johnson	Miss	Mary	Kay	Johnson
10	Dr. Master Surgeon	Dr. MasterSurgeon	Dr. MasterSurgeon	Dr.	Master		Surgeon
Extract Names (TTC)