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.
(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)
myStringWithoutLastWord = mid$(myString, 1, instrrev(trim$(myString), " ")-1)
Since there is no easy way to search backwords in excel (at least that i know of id set a UDF to do this
Function Lastword(ByVal rng As Range, ByVal bolTest As Boolean) As String
Select Case bolTest
Lastword = Mid$(rng.Value, InStrRev(Trim$(rng.Value), " ") + 1)
Lastword = Mid$(rng.Value, 1, InStrRev(Trim$(rng.Value), " ") - 1)