View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Harlan Grove
 
Posts: n/a
Default finding rightmost location of a character

Ron Coderre wrote...
....
With your text in Cell A1
B1: =LEN(A1)+1-MATCH("/",MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&(LEN(A1)))),1),0)
Note: Commit that array formula by holding down the [Ctrl][Shift] keys and
press [Enter].

....

If you're going to use an array of sequential integers, you could use a
shorter, nonarray formula.

=LOOKUP(32768,FIND("/",A1,ROW(INDIRECT("1:"&LEN(A1)))))+1

and you could avoid the volatile INDIRECT using

=LOOKUP(LEN(A1),FIND("/",A1,
ROW(INDEX($1:$65536,1,1):INDEX($1:$65536,LEN(A1),1 ))))+1

The other approach, substituting only the final occurrence of the
substring, may be the best way to go, but it's safer to use a control
character (decimal codes 1-31 and 127) than strings of graphic
characters (all other decimal codes except 0). FWLIW, Windows .CMD
batch files use ^ as a metacharacter, so ^^ represents literal
circumflexes, so using "^^" as the substitution substring would be a
bad idea when parsing .CMD files.