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

daddylonglegs wrote...
Harlan Grove Wrote:

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


Harlan, could you possibly explain the significance of the 32768?


The key is the LOOKUP call. Given how it works, if its 1st argument is
greater than any value in its 2nd argument, it returns the last item in
its last argument with the same type as its 1st argument. Since strings
can't be longer than 32767 characters in Excel, 32768 is guaranteed to
be greater than any numeric value returned by FIND, so the LOOKUP
formula above returns FIND's last numeric result, which corresponds to
the position of the last / in A1. You could use any arbitrarily large
value as the 1st argument to LOOKUP.

BTW I don't believe you need the +1 at the end


Sorry, the +1 advances you to the character position after the last /.
I was paying too much attention to other responses.