Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Splitting/Parsing Names (97 Sr2)

    I have a column that contains names... some are two words, some are 3.
    Example:

    Michael S Smith
    John Smith
    Sam J Jones
    Jackson S Adams
    Leamiel Hernandez

    I want to split out just the last name (the last word). I tried using this formula: =RIGHT(A1,SEARCH(" ",A1,1)+1)
    But, it doesn't work in all cases. For instance, Leamiel Hernandez, it selects ""el Hernandez", not just "Hernandez". I was careful with data entry, and there is no space in the name Leamiel. This happened to about 1/2 the names. There is no pattern to why it doesn't work.

    What did I do wrong?

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting/Parsing Names (97 Sr2)

    See <post#=189016>post 189016</post#>. The equivalent formula to handle the rightmost text after the last space is:

    =RIGHT(A1,LEN(A1)-FIND(CHAR(1),SUBSTITUTE(A1," ",CHAR(1),LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

    Thanks, Steve!
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/Parsing Names (97 Sr2)

    Great! Worked perfectly. But, now the user thought that was so cool, he asked me to split out the first name too. How do I edit your formula to pull out the first name??

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Splitting/Parsing Names (97 Sr2)

    That's the easy part, you were close before:

    =LEFT(A1,FIND(" ", A1))
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Ephrata, Washington, USA
    Posts
    683
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Splitting/Parsing Names (97 Sr2)

    ahhh....thanks much!

Posting Permissions

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