Search for the first space using instr

[Guest]

How can I return the first space in a string using instr function.
Using the following I get the error 'invalid procedure call or argument'

However it works for other characters ok.

Bruce

Function myProduct(a As String)
myProduct = CStr(Left(a, InStr(a, " ") - 1))
End Function

 

[Douglas J. Steele]

Assuming that there actually is a blank character in the string, that should
work. If there isn't a blank, InStr will return 0, so that you will have an
error.

If it's possible not to have blanks, try:

Function myProduct(a As String) As String
Dim lngPos As Long

lngPos = InStr(a, " ")
If lngPos > 0 Then
myProduct = CStr(Left(a, lngPos - 1))
Else
myProduct = a
End If

End Function