split fullname to firstname and lastname

cuongvt · #1 (**permalink**) 12-15-04, 22:42

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)

DavidCoutts · #2 (**permalink**) 12-16-04, 04:07

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

cuongvt · #3 (**permalink**) 12-16-04, 05:41

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

DavidCoutts · #4 (**permalink**) 12-16-04, 06:00

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

shades · #5 (**permalink**) 12-16-04, 20:55

You can also do this with Excel formulas:

See Chip Pearson's Site

cuongvt · #6 (**permalink**) 12-17-04, 03:26

thank you.
All you helped me so much.
thank again

Internet Services

Web Development

Digital Marketing

Consumer Tech