Trim a string after a second space

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
Kostas said:
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"
Click to expand...

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
 
Thanks it works, i get an error message though for some strings that do not
have a family name (only one space in string)...
 
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
 
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!
 
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
 
Back
Top