Results 1 to 7 of 7
  1. #1
    Join Date
    May 2004
    Posts
    133

    Unanswered: newbie: how to split fullname

    Hi all,
    I have a customers query: customerid, fullname, company name etc
    Now I want to split the fullname column into two columns:
    One is firstname ans another one is lastname.
    Ex1: fullname: Vu The Cuong
    -> firstname: Vu The
    -> lastname: Cuong
    Ex2: fullname: Nguyen Hoang Minh Chau
    -> firstname: Nguyen Hoang Minh
    -> lastname: Chau
    Could anyone tell me the trick to solve above problem?
    Please help me, I need your help.
    many thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    several options depnding on the complexity of the name
    1) split function
    2) write a function in vba using the instr fucntion to detect when the desired separator is locted working from the end of the function backwards

    you will however have problems if you have any people with double barreleed names.

  3. #3
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    In your case, you have to put a comma or other separator, between the Name and the first name and the last name, since your first name could consist of more thatn one name using the SPACE as a separator.

    It should be something like that
    fullname: Vu The, Cuong

  4. #4
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Then use the split function as it was suggested

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    theLastWordInMyString = mid$(myString, instrrev(trim$(myString), " ")+1)
    and
    myStringWithoutLastWord = mid$(myString, 1, instrrev(trim$(myString), " ")-1)

    izy
    Last edited by izyrider; 12-15-04 at 10:40. Reason: ooops - forgot the withoutLastWord part
    currently using SS 2008R2

  6. #6
    Join Date
    May 2004
    Posts
    133
    I'm sorry, I'm late.
    I'll try it now
    thanks

  7. #7
    Join Date
    May 2004
    Posts
    133
    [theLastWordInMyString = mid$(myString, instrrev(trim$(myString), " ")+1)
    and
    myStringWithoutLastWord = mid$(myString, 1, instrrev(trim$(myString), " ")-1)
    ]

    many Thanks all
    I tried it. It worked for me. All you are very kind. Now I'm learning above formula.
    thanks again

Posting Permissions

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