On Fri, 30 Jun 2006 12:18:02 -0700, Toppers
wrote:

UDF:

=Getstring(A1,"7h")

assumes a blank between each text pair.

A1 contains string,
second parameter is ID code

Function GetString(ByVal strSearch As String, ByVal delimeter As String)
n1 = InStr(1, strSearch, delimeter)
If n1 = 0 Then
GetString = "no match"
Exit Function
End If
n2 = InStrRev(Left(strSearch, n1 - 2), " ") + 1
GetString = Mid(strSearch, n2, n1 - n2 - 1)
End Function

HTH

Small change to return #VALUE! if delimiter is not present, as OP requested:

========================
Function GetString(ByVal strSearch As String, ByVal delimeter As String)
Dim n1, n2
n1 = InStr(1, strSearch, delimeter)
If n1 = 0 Then
GetString = CVErr(xlErrValue)
Exit Function
End If
n2 = InStrRev(Left(strSearch, n1 - 2), " ") + 1
GetString = Mid(strSearch, n2, n1 - n2 - 1)
End Function
=========================

One other thought, and this really is up to the OP: This UDF will work on a
portion of the delimiter. In other words, it will return Peach if the
delimiter is specified as "4" or as "4h". And if the "fruits" are not all real
fruits, but could occasionally be strings that include a digit, things could
get confusing. (Or if the delimiter was a string that started with a letter).

Whether to take this into account or not would depend on the OP's real value
range for these strings.


--ron