Simple find with right function

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

We have a field of characters similar to:
product/small/GW1400A-GAVE.jpg
We have unsuccessfully tried to use the RIGHT AND FIND functions to
find all characters after the last "/" ( or from the right, all characters
before up to the first /). In other words to extract the GW1400A-9AV.jpg
Perhaps we are using the wrong functions.
Any assistance would be appreciated.
Richard

If you have a column of these thing, then use Text to Columns. Pull-down:

Data > Text to Columns... and follow the wizard. Use the / as the separator.

You will see:


--
Gary''s Student


"Richard Ruda" wrote:

> We have a field of characters similar to:
> product/small/GW1400A-GAVE.jpg
> We have unsuccessfully tried to use the RIGHT AND FIND functions to
> find all characters after the last "/" ( or from the right, all characters
> before up to the first /). In other words to extract the GW1400A-9AV.jpg
> Perhaps we are using the wrong functions.
> Any assistance would be appreciated.
> Richard
>
>
>

Richard,.

I assume there could 1, 2, 3 or any number of /. Assuming so, try this
formula

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

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Richard Ruda" <[email protected]> wrote in message
news:[email protected]...
> We have a field of characters similar to:
> product/small/GW1400A-GAVE.jpg
> We have unsuccessfully tried to use the RIGHT AND FIND functions to
> find all characters after the last "/" ( or from the right, all characters
> before up to the first /). In other words to extract the GW1400A-9AV.jpg
> Perhaps we are using the wrong functions.
> Any assistance would be appreciated.
> Richard
>
>

Hi!

Try this:

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

The tilde character "~" is used as a temporary marker. If your strings might
already contain these characters then use some other character that is not
likely to appear in your strings.

Biff

"Richard Ruda" <[email protected]> wrote in message
news:[email protected]...
> We have a field of characters similar to:
> product/small/GW1400A-GAVE.jpg
> We have unsuccessfully tried to use the RIGHT AND FIND functions to
> find all characters after the last "/" ( or from the right, all characters
> before up to the first /). In other words to extract the GW1400A-9AV.jpg
> Perhaps we are using the wrong functions.
> Any assistance would be appreciated.
> Richard
>
>

Try...

=MID(A1,LOOKUP(2,1/(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)="/"),ROW(INDIRE
CT("1:"&LEN(A1))))+1,1024)

Hope this helps!

In article <[email protected]>,
"Richard Ruda" <[email protected]> wrote:

> We have a field of characters similar to:
> product/small/GW1400A-GAVE.jpg
> We have unsuccessfully tried to use the RIGHT AND FIND functions to
> find all characters after the last "/" ( or from the right, all characters
> before up to the first /). In other words to extract the GW1400A-9AV.jpg
> Perhaps we are using the wrong functions.
> Any assistance would be appreciated.
> Richard

Gary''s Student,

Perfect - thanks

Thanks Bob,
RR

Thanks Biff,
RR

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?

No difference in this case except that mid is more flexible since it can be
used in all position while right or left do what they are named after so
maybe Biff and other always use mid for that reason


--


Regards,

Peo Sjoblom


"DOR" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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 ...