Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Spliting Middle and Last Names Issue
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Banned user
    Join Date
    Jun 2016
    Posts
    149
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Spliting Middle and Last Names Issue

    I wish to split the following names into separate cells. Entries with 1st, middle and last names are split normally into 3 separate columns. But entries with no middle names have an issue. Their last names are placed in the 2nd column as if it were their middle names. How can I fix it so that the last name of entries with no middle name go to the 3rd column (for last names) instead of the 2nd column which is reserved for middle names?

    ann edwards sarah
    yolanda m. dara
    herman lopez
    miike z. boro
    jen yanna
    Last edited by bencar; Oct 8th, 2016 at 07:38 AM.

  2. #2
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    5,006
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  3. #3
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,623
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    This is practically impossible to achieve 100% accuracy.

    Consider these names:

    Betty Jo Spickerman
    Eddie Van Halen

    Jo and Van are not middle names!

    How do you tell Excel that Betty Jo is the first name and Van Halen is the last name?
    .
    Biff
    Microsoft MVP - Excel

    Don't be afraid to use volatile functions or array formulas
    Tell us what version of Excel you're using
    KISS - Keep It Simple Stupid

  4. #4
    Banned user
    Join Date
    Jun 2016
    Posts
    149
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by T. Valko View Post
    This is practically impossible to achieve 100% accuracy.

    Consider these names:

    Betty Jo Spickerman
    Eddie Van Halen

    Jo and Van are not middle names!

    How do you tell Excel that Betty Jo is the first name and Van Halen is the last name?

    But isnt there a formula for that?

  5. #5
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    11,230
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by bencar View Post
    But isnt there a formula for that?
    As Biff states for the examples given, how do you think that Excel would know which is part of the first name and which is part of the Surname unless you had a delimiter like a comma to separate the 2 or a separate list telling it for each name?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  6. #6
    Banned user
    Join Date
    Jun 2016
    Posts
    149
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by MARK858 View Post
    As Biff states for the examples given, how do you think that Excel would know which is part of the first name and which is part of the Surname unless you had a delimiter like a comma to separate the 2 or a separate list telling it for each name?
    Hold on, I'm sure with the mountain of difficult stuff you can pull off on excel theres got to be a way for something as seemingly simple as this. I refuse to believe that. But you got a delimiter as space in between the names. I'm sure theres a formula to split the names accordingly with that.
    Last edited by bencar; Oct 8th, 2016 at 08:31 PM.

  7. #7
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    395
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Unfortunately, there is no formula or VBA code that would be able to differentiate between the middle name the last name that consists of two words. I think this should be easy to understand.

  8. #8
    Board Regular FDibbins's Avatar
    Join Date
    Feb 2013
    Location
    Duncansville, PA USA
    Posts
    6,723
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Yes, you can split on the space, that is not the problem.

    As Tony (Biff) said, the problem comes in with excel being able to identify if the 2nd term is actually the 2nd name, or part of the last name
    Ron de Bruin
    Eric von Lustbarden
    John den Harver

    are all examples where the 2nd term is actually part of the last name. We can see that, but how would excel know?

    On the flip side...
    John Smith 3rd
    Mary Miller Snr

    also have 3 terms, but here, the 3rd term is not the last name

    Robert Mac Arthur
    would Mac be the 2nd name or part of the last? (I know people for both ways)

    To excel, words are just random characters that don't really have any "meaning", so how would it be expected - in a logical, mathematical - manner, to deal with these?

    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    Regards

    Ford

  9. #9
    Board Regular
    Join Date
    Oct 2013
    Location
    Lakeland, FL USA
    Posts
    395
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Enter formula in B1 and pull it across to cell D1 and then down
    =TRIM(MID(SUBSTITUTE(" "&IF(LEN($A1)-LEN(SUBSTITUTE($A1," ",""))=1,SUBSTITUTE($A1," "," "),$A1)," ",REPT(" ",50)),50*COLUMNS($A:A),50))

    Excel 2016 (Windows) 32 bit
    ABCD
    1ann edwards sarahannedwardssarah
    2yolanda m. darayolandam.dara
    3herman lopezhermanlopez
    4miike z. boromiikez.boro
    5jen yannajenyanna

    Sheet3




  10. #10
    Banned user
    Join Date
    Jun 2016
    Posts
    149
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by AlKey View Post
    Unfortunately, there is no formula or VBA code that would be able to differentiate between the middle name the last name that consists of two words. I think this should be easy to understand.
    It would be easy to understand if someone could explain it simply.

Some videos you may like

User Tag List

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
  •