Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

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

"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

My apologies, but I am a little unfamilar with creating the function call.
Would I create a macro called "GetString" then

Sub GetString()
... details below?
End Sub

Regards,
Dan

"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

"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

Copy and paste the macro code (Function Getstring ....) below into a module
and then in a cell , e.g B1, put:


=Getstring(A1,"7h")

Where A1 contains your string and "7h" is the ID

B1 will contain (or should!) the text associated with the ID.

This works in the same as Excel functions such as SUM etc.

Copy/paste this 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

"Dan" wrote:

My apologies, but I am a little unfamilar with creating the function call.
Would I create a macro called "GetString" then

Sub GetString()
... details below?
End Sub

Regards,
Dan

"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

"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

Thanks for the assistance. Works great.

"Toppers" wrote:

Copy and paste the macro code (Function Getstring ....) below into a module
and then in a cell , e.g B1, put:


=Getstring(A1,"7h")

Where A1 contains your string and "7h" is the ID

B1 will contain (or should!) the text associated with the ID.

This works in the same as Excel functions such as SUM etc.

Copy/paste this 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

"Dan" wrote:

My apologies, but I am a little unfamilar with creating the function call.
Would I create a macro called "GetString" then

Sub GetString()
... details below?
End Sub

Regards,
Dan

"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

"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

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

Maybe something along these lines........

=IF(ISERR(FIND(B1,A1,1)),"",IF(FIND(B1,A1,1)0,LOO KUP(B1,{"3h","7h"},{"Melon","Bananna"})))

Vaya con Dios,
Chuck, CABGx3



"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

This will only work if I know the IDs and the variables (information within
the LOOKUP). Unfortunately I will only be given an ID, 0h, 1h, 2h, ...

Regards,

Dan

"CLR" wrote:

Maybe something along these lines........

=IF(ISERR(FIND(B1,A1,1)),"",IF(FIND(B1,A1,1)0,LOO KUP(B1,{"3h","7h"},{"Melon","Bananna"})))

Vaya con Dios,
Chuck, CABGx3



"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan

My UDF (earlier posting) addresses this problem - ID only is required.

"Dan" wrote:

This will only work if I know the IDs and the variables (information within
the LOOKUP). Unfortunately I will only be given an ID, 0h, 1h, 2h, ...

Regards,

Dan

"CLR" wrote:

Maybe something along these lines........

=IF(ISERR(FIND(B1,A1,1)),"",IF(FIND(B1,A1,1)0,LOO KUP(B1,{"3h","7h"},{"Melon","Bananna"})))

Vaya con Dios,
Chuck, CABGx3



"Dan" wrote:

Trying to extract text within a string (do not know the length or content).
Only information I will be given is the ID corresponding to the text needed
to extract.

String:
Apple 0h Orange 1h Pear 2h Melon 3h Peach 4h Bananna 7h

For the ID "3h", I need to return Melon.
For the ID "7h", I need to return Bananna.

There is the possibility of an ID not in the string, i.e. 5h which should
return #VALUE!. The problem is 7h.

Thanks

Dan