Spliting Middle and Last Names Issue
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

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

  1. #11
    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

    Using AlKey's suggestion...
    A
    B
    C
    D
    1
    ann edwards sarah ann edwards sarah
    2
    yolanda m. dara yolanda m. dara
    3
    herman lopez herman lopez
    4
    miike z. boro miike z. boro
    5
    jen yanna jen yanna
    6
    Ron de Bruin Ron de Bruin
    7
    Eric von Lustbarden Eric von Lustbarden
    8
    John den Harver John den Harver
    9
    John Smith 3rd John Smith 3rd
    10
    Mary Miller Snr Mary Miller Snr


    This shows my point even better

    - 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

  2. #12
    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

    Quote Originally Posted by bencar View Post
    It would be easy to understand if someone could explain it simply.
    That's what I attempted to do in post 8

    - 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

  3. #13
    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
    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
    A B C D
    1 ann edwards sarah ann edwards sarah
    2 yolanda m. dara yolanda m. dara
    3 herman lopez herman lopez
    4 miike z. boro miike z. boro
    5 jen yanna jen yanna
    Sheet3
    I did as you asked. But I still get 'Lopez' in the C column, not D column.

    Also, how were you able to paste your excel sheet in your previous post?

  4. #14
    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

    Quote Originally Posted by bencar View Post
    But you got a delimiter as space in between the names. I'm sure theres a formula to split the names accordingly with that.
    Yes, if the names contain just 2 words, a 1 word first name and a 1 word last name, then it's fairly simple to parse those names:

    John Smith
    Karen Kolter

    However, when names have more than 2 words it gets complicated.

    Betty Jo Spickerman
    Eddie Van Halen

    Betty Jo is the first name and Spickerman is the last name.

    Eddie is the first name and Van Halen is the last name.

    Both names are 3 words. So, what logic can we use to tell Excel that the first example has a 2 word first name but the second example has a 2 word last name?

    We can't just use the number of space characters. They both have the same number of space characters but the space needed to split is not the same.
    .
    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

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

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by bencar View Post
    I did as you asked. But I still get 'Lopez' in the C column, not D column.

    Also, how were you able to paste your excel sheet in your previous post?
    This is because there is a trailing spaces. Remove trailing space from herman lopez and the formula will readjust.

  6. #16
    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
    This is because there is a trailing spaces. Remove trailing space from herman lopez and the formula will readjust.
    I dont understand. What do you mean by trailing space and where are they in 'herman lopez'? I checked in the cell and I dont see extra spaces before or after 'herman lopez'.

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

    Default Re: Spliting Middle and Last Names Issue

    Quote Originally Posted by bencar View Post
    I dont understand. What do you mean by trailing space and where are they in 'herman lopez'? I checked in the cell and I dont see extra spaces before or after 'herman lopez'.
    I added trim function that will remove extra spaces
    Try this
    =TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," "," "),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))

  8. #18
    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
    I added trim function that will remove extra spaces
    Try this
    =TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," "," "),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))
    I still get the same result. After I paste your formula and drag the cells across and down the result is still the same.

  9. #19
    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

    Regardless of whether the formula works, you are still left with the situation that it will never be 100% reliable/accurate, based on the comments made above

    - 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

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

    Default Re: Spliting Middle and Last Names Issue

    All formulas worked find for me. This would be the last try.
    =TRIM(MID(SUBSTITUTE(" "&IF(LEN(TRIM($A1))-LEN(SUBSTITUTE(TRIM($A1)," ",""))=1,SUBSTITUTE(TRIM($A1)," ",REPT(" ",2)),TRIM($A1))," ",REPT(" ",50)),50*COLUMNS($A:A),50))

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
  •