EXCEL: How to scan text reversed (like ACCESS: InStrRev)?
"4mula_freak" wrote...
Immediate Purpose: To do a right-to-left scan in EXCEL formula operating
on another text field, similar to what's allowed in ACCESS query formula
with InStrRev function. . . .
....
A work-around is bulky: Make your own reversed text, then use normal
left-to-right FIND and offsset this against LENgth to get answer. . . .
....
And yet another way involves using a few defined names.
seq: =ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,1024,1))
Then the rightmost position of the substring x in y would be given by
=LOOKUP(2,1/(MID(y,seq,LEN(x))=x),seq)
And if you want to use wildcards,
=LOOKUP(2,1/(SEARCH(x,MID(y,seq,LEN(y)))=1),seq)
Granted this isn't exactly obvious, but it works WITHOUT array entry or
volatile functions. But this is what you get when misusing a program
intended for numeric financial calculations for text manipulation and/or
general list processing.
|