worksheet function for InStrRev()

[Martin]

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!

[Bob Phillips]

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" <Martin@discussions.microsoft.com> wrote in message
news:4445F4DE-5C3C-4F66-B5AF-6D18D440D736@microsoft.com...
> 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!

[Tom Ogilvy]

=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" <Martin@discussions.microsoft.com> wrote in message
news:4445F4DE-5C3C-4F66-B5AF-6D18D440D736@microsoft.com...
> 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!

[Martin]

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" <Martin@discussions.microsoft.com> wrote in message
> news:4445F4DE-5C3C-4F66-B5AF-6D18D440D736@microsoft.com...
> > 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!
>
>
>

[jefkve]

Not sure how helpfull this is 6+ years later, but here goes: I had addresses put into a string that i needed to parse. Obviously these could vary wildly. Therefore with the following address in cell D2 :

1234 S Main St. Chicago IL 60609

I had already parsed the ZIP to column I, and State to Column E was trying to parse the city and I came up with the following:

=TRIM(MID(D2,FIND("~",SUBSTITUTE(D2," ","~",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))-2)),LEN(D2)-FIND("~",SUBSTITUTE(D2," ","~",LEN(D2)-LEN(SUBSTITUTE(D2," ",""))-2))-LEN(E2&" "&I2)))

Basically it works like this: It counts the number of spaces (6) subtracts the number of spaces I have already taken care of (2), then I use this in my substitute to replace the 4th space with a character (could have used a special character, but opted for a tilde), and then searched that string for my special character to get my starting location. Then you just have to solve for the number of character you want (total len - found start - used chars) to use in your mid statement. Trim was just superflous, but I couldn't help myself

FYI: This doesnt work for cities with more that one name .... oh well....