Simple find with right function

Hi!

Am I missing something, or does RIGHT have some obscure problem that
the experts are aware of and others are not?

No, not really. Using RIGHT is perfectly acceptable but consider these
differences (and this is what separates the "experts" from the rest of us!):

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

That formula contains 7 function calls and takes 86 keystrokes.

=MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255)

This formula has 6 function calls and takes 82 keystrokes.

Biff

"DOR" wrote in message
oups.com...
Biff,

I notice that 3 different responders to this request suggest the MID
function with some high number of characters to return, rather than the
RIGHT function as in :

=RIGHT(A1,LEN(A1)-FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/","")))))

which seems to work well enough and doesn't need the catch-all value of
1,255 or whatever else.

Am I missing something, or does RIGHT have some obscure problem that
the experts are aware of and others are not?

Biff and Peo,

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

I guess Biff could have saved a few keystrokes and a few interpreter
cycles by leaving out the comma in 1,255 or using a smaller number g
....

DOR

I guess Biff could have saved a few keystrokes and a few interpreter
cycles by leaving out the comma in 1,255 or using a smaller number g

The comma is the argument delimiter in the MID function!

=MID(A1,FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1,255)

FIND("~",SUBSTITUTE(A1,"/","~",LEN(A1)-LEN(SUBSTITUTE(A1,"/",""))))+1

That is the 2nd argument which tells MID at what position in the string to
use as the extraction starting point.

255 is the 3rd argument which tells MID how many characters to return.

255 is used because you may never know exactly how many characters there are
to the end of the string.

Biff

"DOR" wrote in message
oups.com...
Biff and Peo,

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

I guess Biff could have saved a few keystrokes and a few interpreter
cycles by leaving out the comma in 1,255 or using a smaller number g
...

DOR

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.

But of course, Biff, but of course ...