Extract text from String

[Dan]

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

 

[Toppers]

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

 

[CLR]

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

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

Vaya con Dios,
Chuck, CABGx3

 

[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

 

[Toppers]

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

 

[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]

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]

Thanks for the assistance. Works great.

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

 

[Ron Rosenfeld]

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