EXCEL USER-DEFINED FUNCTION USING EXCEL TEXT FUNCTION

Replace Search with WorksheetFunction.Find (NT)

So are you trying to return the portion of the text to the right of the delimiter you pass in?

For example, if you had the string:

Hello World;_Goodbye Sun

If would return:

_Goodbye Sun

If you passed a semi-colon (;) as the delimiter.

If so, say cell A1 had the string mentioned above, and B1 had a semi-colon. You could easily write a worksheet formula for this:

=RIGHT(A1, LEN(A1)-FIND(B1,A1))

If you really wanted a user-defined function, it would look something like this (you need to make sure that you put a return type for the function, or it won't return anything):

'******************************************************************************
Public Function Whatever(strText as String, strDelim as String) as String

Whatever = Right(strText, Len(strText) - InStr(strText, strDelim)+1)

End Function
'******************************************************************************

Or, an even better way:

'******************************************************************************
Public Function Whatever(strText as String, strDelim as String) as String

Whatever = Mid(strText, InStr(strText, strDelim)+1)

End Function
'******************************************************************************

Hope this helps,

Russell

Len, right, left are members of the string class
in your VBA application.
Search is a member of the worksheet function class
under the Excel library.

So define it as Application.Worksheetfunction.search etc

Ivan