Split surname and name in two strings

[raphael99]

Hi I have surname and name in the same column.
I want them separate (splitted)
For the surname I use :

Firstname = Split(fullname," ")(0)

and that return firstname.
All is ok.
But afetr there is a space and then the name.
How to extract the name after the space?

 

[Gasman]

Use instr() to find the space and then use Left() , Right() or Mid() ?

 

[missinglinq]

There are a number of ways to do this...this is the routine I ran up for someone a while back, using the same approach the Gasman suggested:

Me.LastName = Left(Me.Wholename, InStr(Me.Wholename, " "))

Me.FirstName = Right(Me.Wholename, Len(Me.Wholename) - InStr(Me.Wholename, " "))

This is dependent, of course, in the names always being entered in the format

LastName FirstName

with a Space between the two, not a Comma, as is commonly done. If that's a possibility, you can check for the Comma and then act accordingly

If InStr(Me.Wholename, ",") > 0 Then
 
 Me.LastName = Left(Me.Wholename, InStr(Me.Wholename, ",")-1)

 Me.FirstName = Right(Me.Wholename, Len(Me.Wholename) - InStr(Me.Wholename, ",") - 1)

Else

 Me.LastName = Left(Me.Wholename, InStr(Me.Wholename, " "))

 Me.FirstName = Right(Me.Wholename, Len(Me.Wholename) - InStr(Me.Wholename, " "))

End If

Linq ;0)>

 

[raphael99]

Hi MissingLinq
your code:

Me.LastName = Left(Me.Wholename, InStr(Me.Wholename, " "))

Me.FirstName = Right(Me.Wholename, Len(Me.Wholename) - InStr(Me.Wholename, " "))

Does works nice. But there is a problem. What if the name is for example more words. in eg. Van Helen Robert
Infact the code report : Last Name Van, Firstname Helen Robert
How can I arrange?

 

[Gasman]

Raphael99,

PMFJI, use InstrRev instead to find the position of the last space

 

[raphael99]

Can you please show an example?
I suppose it is the second code...am I right?
Using the second code it is the same: it splits like this
LASTNAME: VAN
Firstname: HALEN ROBERT

 

[Gasman]

All you would be doing is locating the position of the last space character and split accordingly

Dim strName As String, strFirst As String, strLast As String
Dim iPos As Integer
strName = "Halen Robert Van"
iPos = InStrRev(strName, " ")
strFirst = Left(strName, iPos - 1)
strLast = Right(strName, Len(strName) - iPos)
Debug.Print strFirst
Debug.Print strLast

HTH

 

[missinglinq]

...What if the name is for example more words. in eg. Van Helen Robert
Infact the code report : Last Name Van, Firstname Helen Robert...

Sorry, Helen Robert is not a 'first name,' it's a first name and middle name! As a rule, you get answers, here, based on your posted question, in this case

"I have surname and name in the same column"

As I said in my reply

"This is dependent, of course, in the names always being entered in the format

LastName FirstName"

I believe you're going to find that it's easier to educate you users to enter data in this way, than trying to write code to cover every possible variation in names, because this always ends up being a hairball! Without user education, in addition to

Helen Robert Van

you have to worry about things like

Reverend Martin Luther King

Clarence Darrow, Esq.

Lt Col R Cartwright

Mrs Maria von Trapp

Maj. Charles Emerson Winchester III

and the variations go on and on and on! You can code to cover all of this, but it's not simple!

Linq ;0)>