Left Lookup Custom function

[Jacob]

Hello all I am trying to write a custom function to do the work of my
current function, so that all I have to do is type the custom function
name in and select the cell. This is what I have so far, but is not
working for me. As I am new to Custom Functions some help would be
greatly appreciated.


This is the original function

=Left(A2,Search(" ",A2,1)-1)

This is what I am trying to use in VBA

Function LeftSearch(textStr As Variant)

LeftSearch = Left(textStr, Search(" ",textStr,1) - 1)


End Function

Thanks in Advance

Jacob

 

[Dave Peterson]

VBA has the equivalent of =Search() in its InStr.

But you'll find that the UDF you create will be slower than using that formula
you want to dump.

Option Explicit
Function LeftSearch(textStr As String) As String
Dim SpacePos As Long
SpacePos = InStr(1, textStr & " ", " ", vbTextCompare)
LeftSearch = Left(textStr, SpacePos - 1)
End Function

 

[Jacob]

Thanks Dave, This works great.