Extract text from String

D

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
 
T

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
 
C

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
 
D

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
 
D

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
 
T

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
 
D

Dan

Thanks for the assistance. Works great.

Toppers said:
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
 
R

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top