Searching for string within text

[dugdugdug]

I am using the function Instr to search for a string within some text.

My list contains Apples and Applesauce.

How can I search for only Apples because I seem to be returning Applesuace as well.

Obviously the other way round is not a problem because searching for Applesauce will not return Apples.

Thanks

 

[NBVC]

Something like:

InStr(" "&Range("A1")&" "," apples ")

i.e. add a space to search string, and space to A1 reference in cases Apples is at the beginning/end of the string.

 

[Jonmo1]

Try adding spaces to the beginning and end of the original string,
Then searching for the word also with spaces at the beginning and end..

Sub test()
mystr1 = "this is apples dude"
mystr2 = "and this is applesause my friend"
x = InStr(1, " " & mystr1 & " ", " apples ")
y = InStr(1, " " & mystr2 & " ", " apples ")
End Sub

 

[dugdugdug]

Something like:

InStr(" "&Range("A1")&" "," apples ")

i.e. add a space to search string, and space to A1 reference in cases Apples is at the beginning/end of the string.

Try adding spaces to the beginning and end of the original string,
Then searching for the word also with spaces at the beginning and end..

Sub test()
mystr1 = "this is apples dude"
mystr2 = "and this is applesause my friend"
x = InStr(1, " " & mystr1 & " ", " apples ")
y = InStr(1, " " & mystr2 & " ", " apples ")
End Sub

Sorry, I wasn't specific enough.

I have abcApplesedf
and efgApplesauceijk

so putting spaces would not help.

 

[NBVC]

You might have to use an AND to disqualify the "Applesauce"

e.g.

IF InStr(Range("A1"),"Apples")>0 And InStr(Range("A1"),"Applesauce")= 0 then
 x = InStr(Range("A1"),"Apples")

</pre>