Trim a string after a second space

[Kostas Arvanitidis]

Hello
I have a text field that contains 2 or more words (Name,Lastname,Familyname)
The words are seperated by a space. How can i trim out the whole string from
the second space?
i.e.
"John Smith Jameson" --> "John Smith"

 

[Smartin]

Hello
I have a text field that contains 2 or more words (Name,Lastname,Familyname)
The words are seperated by a space. How can i trim out the whole string from
the second space?
i.e.
"John Smith Jameson" --> "John Smith"

Hello,

One way:

The first space is given by
(A) FirstSpace = Instr(1, InputString, " ")

The second space is given by
(B) SecondSpace = Instr(FirstSpace + 1, InputString, " ")

The part you want to keep is apparently
(C) Left(InputString, SecondSpace - 1)

Put it all together in a public function
Public Function FirstTwoWords (InputString As String) As String
Dim FirstSpace As String
Dim SecondSpace As String
FirstSpace = Instr(1, InputString, " ")
SecondSpace = Instr(FirstSpace + 1, InputString, " ")
FirstTwoWords = Left(InputString, SecondSpace - 1)
End Function

HTH

 

[Kostas Arvanitidis]

Thanks it works, i get an error message though for some strings that do not
have a family name (only one space in string)...

 

[Smartin]

Aah, such is life. We can't always rely on the data to fit into our nice
little boxes.

Well, we could write some error handling but my guess is if no second
space exists then /SecondSpace/ = 0, causing the /Left/ evaluation to
fail. So let's try this:

Public Function FirstTwoWords (InputString As String) As String
Dim FirstSpace As String
Dim SecondSpace As String
FirstSpace = Instr(1, InputString, " ")
SecondSpace = Instr(FirstSpace + 1, InputString, " ")
If SecondSpace = 0 Then
FirstTwoWords = InputString)
Else
FirstTwoWords = Left(InputString, SecondSpace - 1)
EndIf
End Function

 

[Smartin]

Well duh me, I just realized I dimensioned the variables as String...
should be Long or Integer.

That's what I get for writing code offline...

You must have figured that out already (^: Sorry!

 

[Kostas Arvanitidis]

Perfect....
I hope someday i'll be as helpful as you were...
Thank you very much...

 

[Smartin]

Glad it works! Good luck to you.

Perfect....
I hope someday i'll be as helpful as you were...
Thank you very much...

 

[Ken Sheridan]

The following will return the substring before the last space in a string,
regardless of how many spaces there are:

Const NOSPACES = 5
Dim strFullname As String, strFirstNames As string

strFullName = <get string expression from somewhere>

On Error Resume Next
strFirstNames = Left(strFullName,InstrRev(strFullname, " ")-1)
Select Case Err.Number
Case 0
' no error
Case NOSPACES
' no spaces in string so return full string
strFirstNames = strFullName
Case Else
' unknown error so inform user
MsgBox Err.Description, vbExclamation, "Error"
End Select

Debug.Print strFirstnames

Ken Sheridan
Stafford, England