Find Function
 

Hi
Cell A2=aaaa-bbb-cccc-d
in A1 I would like a formula to find the second "-" from the right and
return cccc-d. The number of characters in each segement of A2 can vary and
thennumber of segments may vary as well, so the search will have to be from
the right.
Thanks

Brute force :-):

=RIGHT(A2,LEN(A2)-FIND("/",SUBSTITUTE(A2,"-","/",LEN(A2)-LEN(SUBSTITUTE(A2,"
-",""))-1)))

--

Vasant

"Steve" wrote in message
...
Hi
Cell A2=aaaa-bbb-cccc-d
in A1 I would like a formula to find the second "-" from the right and
return cccc-d. The number of characters in each segement of A2 can vary
and
thennumber of segments may vary as well, so the search will have to be
from
the right.
Thanks

Vasant Nanavati wrote...
Brute force :-):

=3DRIGHT(A2,LEN(A2)-FIND("/",SUBSTITUTE(A2,"-","/",
LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))-1)))
..=2E.

You could save a function call and shorten this to

=3DMID(A2,FIND("=AC",SUBSTITUTE(A2,"-","=AC",
LEN(A2)-LEN(SUBSTITUTE(A2,"-",""))-1))+1,256)

but that leaves the quibble that if the string could contain any
characters, the robust approach would be an array formula like

=3DMID(A2,LARGE(IF(MID(A2,ROW(INDIRECT("1:256")),1 )=3D"-",
ROW(INDIRECT("1:256"))),2)+1,256)