Find Function

[Guest]

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]

Brute force :

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

 

[Harlan Grove]

Vasant Nanavati wrote...

Brute force :

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

....

You could save a function call and shorten this to

=MID(A2,FIND("¬",SUBSTITUTE(A2,"-","¬",
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

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