Find WorksheetFunction - finding text within text

[Guest]

I have a long list of strings. I want to return the first part of each
string, up to the first space or foward slash ("/"). I can't seem to use the
Find function correctly (does it only work for searching through ranges?).

Here is my code - not the most elegant... any ideas?

Function ExtractClient(mystr As String) As String

Dim i As Single
Dim SpacePos As Single
Dim SlashPos As Single
Dim NoSpace As Boolean
Dim NoSlash As Boolean
Dim MinDelim As Integer

NoSpace = False
NoSlash = False

With Application.WorksheetFunction
If .IsError(.Find(" ", mystr, 1)) = True Then
'no space in string
NoSpace = True
Else
SpacePos = .Find(" ", mystr, 1)
End If

If .IsError(.Find("/", mystr, 1)) = True Then
'no slash in string
NoSlash = True
Else
SlashPos = .Find("/", mystr, 1)
End If

If NoSlash = True And NoSpace = True Then
ExtractClient = ""
Else
If NoSlash = True Then
ExtractClient = .Left(mystr, SpacePos - 1)
Else
If NoSpace = True Then
ExtractClient = .Left(mystr, SlashPos - 1)
Else
MinDelim = .Min(SpacePos, SlashPos)
ExtractClient = .Left(mystr, MinDelim - 1)
End If
End If
End If
End With

End Function

 

[Tom Ogilvy]

don't use the worksheetfunction Find. Use the VBA function Instr

If you must go with your current code, remove WorksheetFunction. so just
Application. remains. then, given it is otherwise written correctly, it
will work.