Splitting out title, first name, middle name, last name
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

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

  1. #1
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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

  2. #2
    New Member
    Join Date
    Aug 2019
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name


  3. #3
    Board Regular tyija1995's Avatar
    Join Date
    Feb 2019
    Posts
    648
    Post Thanks / Like
    Mentioned
    14 Post(s)
    Tagged
    4 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

    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
    √-1 2³ ∑ π
    …And it was delicious!

  4. #4
    New Member
    Join Date
    Aug 2019
    Posts
    34
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

    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?

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

    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
    4PrefixFirstMiddleLast
    5Mr. Joe X BoggsMr.Joe X Boggs
    6Mr. William Mark SmithMr.William Mark Smith
    7Mr. Samuel Oscar SmitheMr.Samuel Oscar Smithe
    8Miss Susan JonesMissSusan Jones
    9Miss Mary Kay JohnsonMissMary Kay Johnson
    10Dr. Master SurgeonDr.Master Surgeon

    Sheet1



    Worksheet Formulas
    CellFormula
    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))


  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

    What happens if you have a name like
    Mr Joost van der Westhuizen
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,094
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

    Quote Originally Posted by Fluff View Post
    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.
    Last edited by Rick Rothstein; Aug 21st, 2019 at 02:03 PM.
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  8. #8
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    600
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

    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.
    Last edited by kweaver; Aug 21st, 2019 at 02:10 PM.

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    26,897
    Post Thanks / Like
    Mentioned
    459 Post(s)
    Tagged
    45 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

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

    Last edited by Fluff; Aug 21st, 2019 at 02:11 PM.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Aug 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Splitting out title, first name, middle name, last name

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

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •