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

O

OP193

New Member
Joined
Aug 21, 2019
Messages
3
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
 
Sort by date Sort by votes
You can use my UDF function
Code: 
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
EFGHIJ
Mr Joe x BloggsMrJoexBloggs
Mr Adam Tom Jerry MouseMrAdamTomJerryMouse

<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]
 
Upvote 0

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
salim hasan said:
You can use my UDF function
Code: 
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
Click to expand...
While your UDF does not address what Fluff, kweaver and myself pointed out, I do note that your UDF can be simplified to this...
Code: 
Function Salim_Split_Name(N_name, N)
  On Error GoTo BadN
  Salim_Split_Name = Split(N_name)(N - 1)
BadN:
End Function
 
Last edited:
Upvote 0
kweaver said:
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:

EFGHI
PrefixFirstMiddleLast
Mr. Joe X BoggsMr.Joe X Boggs
Mr. William Mark SmithMr.William Mark Smith
Mr. Samuel Oscar SmitheMr.Samuel Oscar Smithe
Miss Susan JonesMissSusan Jones
Miss Mary Kay JohnsonMissMary Kay Johnson
Dr. Master SurgeonDr.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]
Click to expand...

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
 
Upvote 0
OP193 said:
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
Click to expand...
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
EFGHI
4PrefixFirstMiddleLast
5Mr. Joe X BoggsMr.JoeXBoggs
6Mr. William Mark SmithMr.WilliamMarkSmith
7Mr. Samuel Oscar SmitheMr.SamuelOscarSmithe
8Miss Susan JonesMissSusanJones
9Miss Mary Kay JohnsonMissMaryKayJohnson
10Dr. Master SurgeonDr.MasterSurgeon
Extract Names
 
Last edited:
Upvote 0
kweaver said:
Nicely done, Peter! (of course)
Click to expand...
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
EFGHIJK
5Mr. Joe X BoggsMr. Joe X BoggsMr. Joe X BoggsMr.JoeXBoggs
6Mr. William Mark SmithMr. William Mark SmithMr. William Mark SmithMr.WilliamMarkSmith
7Mr. Samuel Oscar SmitheMr. Samuel Oscar SmitheMr. Samuel Oscar SmitheMr.SamuelOscarSmithe
8Miss Susan JonesMiss SusanJonesMiss SusanJonesMissSusanJones
9Miss Mary Kay JohnsonMiss Mary Kay JohnsonMiss Mary Kay JohnsonMissMaryKayJohnson
10Dr. Master SurgeonDr. MasterSurgeonDr. MasterSurgeonDr.MasterSurgeon
Extract Names (TTC)
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
How to split a full name in 1 column into 2 columns (C1 - First name, C2 - Last name (*but here's the tricky - what if there's a middle initial?
Replies
6
Views
318
Mavericx
M
A
  • Solved
Split First Name & Last Name If Last Name Exists
Replies
5
Views
249
Peter_SSs
Peter_SSs
A
  • Question Question
Returning customers ID from their first, middle and surname
Replies
2
Views
611
Peter_SSs
Peter_SSs
T
  • Solved
Automatically generate dates for specific days of the week on any given month
Replies
6
Views
177
Peter_SSs
Peter_SSs
I
  • Solved
How to split last name from multiple first/middle names
2
Replies
11
Views
2K
Joe4
Joe4

Forum statistics

Threads
1,223,236
Messages
6,170,912
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top