Is there such a thing? I know there's SEARCH() which is equivalent to
InStr() in VBA but now we have InStrRev() which searches for a string from
the end rather than the beginning. Be a lot more useful than CRITBINOM() etc!

Here is one way

=MAX(IF(MID(A10,ROW(INDIRECT("1:"&LEN(A10))),1)="~ ",ROW(INDIRECT("1:"&LEN(A1
0)))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Martin" wrote in message
...
Is there such a thing? I know there's SEARCH() which is equivalent to
InStr() in VBA but now we have InStrRev() which searches for a string from
the end rather than the beginning. Be a lot more useful than CRITBINOM()
etc!

=FIND("~",SUBSTITUTE(D6,"the","~",(LEN(D6)-LEN(SUBSTITUTE(D6,"the","")))/LEN
("the")))

Entered with Ctrl+Shift+Enter since it is an array formula.

Searches for the last occurance of "the" as an example.

--
Regards,
Tom Ogilvy


"Martin" wrote in message
...
Is there such a thing? I know there's SEARCH() which is equivalent to
InStr() in VBA but now we have InStrRev() which searches for a string from
the end rather than the beginning. Be a lot more useful than CRITBINOM()
etc!

thanks, both to Tom and Bob.

Another way I've just found is to create a VBA user function in a module
(best in personal.xls so it's always available):

Function myReverse(stringtocheck As String, stringtomatch As String, startas
As Long)
myReverse = InStrRev(stringtocheck, stringtomatch, startas)
End Function


"Tom Ogilvy" wrote:

=FIND("~",SUBSTITUTE(D6,"the","~",(LEN(D6)-LEN(SUBSTITUTE(D6,"the","")))/LEN
("the")))

Entered with Ctrl+Shift+Enter since it is an array formula.

Searches for the last occurance of "the" as an example.

--
Regards,
Tom Ogilvy


"Martin" wrote in message
...
Is there such a thing? I know there's SEARCH() which is equivalent to
InStr() in VBA but now we have InStrRev() which searches for a string from
the end rather than the beginning. Be a lot more useful than CRITBINOM()
etc!