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

    Unanswered: split fullname to firstname and lastname

    I have a column: fullname
    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
    (I was guided about above problem in MS Access and it worked for me, could anyone tell me the trick in MS Excel also)
    thank you in advanced
    Code in MS access:
    theLastWordInMyString = mid$(myString, instrrev(trim$(myString), " ")+1)
    and
    myStringWithoutLastWord = mid$(myString, 1, instrrev(trim$(myString), " ")-1)

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Since there is no easy way to search backwords in excel (at least that i know of id set a UDF to do this

    i.e.
    Code:
    Function Lastword(ByVal rng As Range, ByVal bolTest As Boolean) As String
        
        
        Select Case bolTest
            Case True
                Lastword = Mid$(rng.Value, InStrRev(Trim$(rng.Value), " ") + 1)
            Case False
                Lastword = Mid$(rng.Value, 1, InStrRev(Trim$(rng.Value), " ") - 1)
        End Select
    End Function
    hth
    Dave

  3. #3
    Join Date
    May 2004
    Posts
    133
    Thank for help
    I'm not know much about VBA in Excel. Could you tell me where I must put above function so that it take effect.
    thank you

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ok, If you get into the VBE(Press Alt+F11)
    Go to Insert then Choose Module
    Paste the code there,
    Now to work with this on your Worksheet go back to excel, Choose the correct cell and Type

    =LastWord(A1,True)
    (assuming A1 is the cell you want to use)
    True Indicates the last word
    Replace this with false for everything else

    Dave

  5. #5
    Join Date
    Oct 2003
    Posts
    1,091
    You can also do this with Excel formulas:

    See Chip Pearson's Site
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  6. #6
    Join Date
    May 2004
    Posts
    133
    thank you.
    All you helped me so much.
    thank 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
  •