instr backwards ?

I`m trying to write a program to search for a word in a sentence but backwards !!

Take a scentence like this, I want to search for the word "ate" then extract the word monkey to the left of it.

"The monkey ate all the bananas"

For it to work in my program the way I want to I cant search between the words "The" & "ate" - hard to explain but i dont want it this way.

What the program needs to do is find the word "Ate" then work backwards untill it gets to the word "the" in the sentence.

as simply as possible, using either left,split,instr ect...

cheerz

Really basic example, doesn't handle for the fact that the search term may be the first word...

VB Code:

Dim strTest As String, strArray() As String
    strTest = "The monkey ate all the bananas"
 
    strArray = Split(strTest)
    
    For i = 0 To UBound(strArray)
        If strArray(i) = "ate" Then
            MsgBox strArray(i - 1)
            Exit For
        End If
    Next

hope that helps :/

not sure if this is quite what you are looking for but the 'instr backwards' function is InStrRev

Description

Returns the position of an occurrence of one string within another, from the end of string.

Syntax

InstrRev(string1, string2[, start])

The InstrRev function syntax has these parts:

Part Description
string1 Required.String expression being searched.
string2 Required. String expression being searched for.
start Optional.Numeric expression that sets the starting position for each search. If omitted, –1 is used, which means that the search begins at the last character position. If start contains
Null, an error occurs.

See MSDN for a more detailed description.

Hope that helps

:)