View Single Post
  #14   Report Post  
Harlan Grove
 
Posts: n/a
Default Simple find with right function

DOR wrote...
Thank you both - flexibility and efficiency trump intuitivity ...

....

Intuitivity, if it were an English word, I suppose would mean
intuitiveness. Intuitiveness is subjective.

Shorter formulas may not matter much when the overall formula length is
fewer than 100 characters, but small formulas have a bad habbit of
becoming small pieces of much longer formulas, and when formulas grow
to several hundred characters and many repeated expressions, it's good
to know how to shorten them.

Also, Excel's substring matching functions only return position from
the left of the string. If one wants all characters to the right of a
given position (measured from the left), and if Excel's substring
functions were picky, there'd be two ways to return it:

RIGHT(string,LEN(string)-position)

and

MID(string,position+1,LEN(string)-position)

Happily, these functions aren't picky, and effectively truncate their
length arguments at the length of their string argument. Which means
the MID call above is equivalent to

MID(string,position+1,LEN(string))

and

MID(string,position+1,<BIGNUMBER)

where <BIGNUMBER is a placeholder for any moderately large positive
whole number. And, FWIW, VBA's Mid function doesn't even require a 3rd
argument to return the right substring beginning at the position given
by its second argument. Using the worksheet MID function with a large
3rd argument is the simplest way to mimick VBA Mid semantics.

That said, this is a situation in which using RIGHT may be more
suitable. The shortest formula not using defined names may be the array
formula

=RIGHT(A1,MATCH("/",MID(A1,LEN(A1)-ROW(INDIRECT("1:"&LEN(A1)))+1,1),0)-1)

Using defined names, e.g., s referring to =ROW(INDIRECT("1:1024")), the
shortest formula would be

=MID(A1,LOOKUP(2,1/(MID("/"&A1,s,1)="/"),s),1024)

which should return the whole string if there are no /s in it.

If you want to get exotic, download and install Laurent Longre's
MOREFUNC.XLL add-in and use

=WMID(A11,-1,1,"/")

and learn the joys of using negative integers to index from right to
left.