Search in a cell from right to left instead of left to right

[BVOPP]

in my spreadsheet there are a lot of cells that contain a text like "T:\DEPLOY\NETHERLAND\SAPBEHEER\SOURCESPACKAGE\ZCB\ABAP/CLASSPOOL/ZCL_CB_PROJECT/CLASS/ZCL_CB_PROJECT/METHOD/SET_PROJECT_SUB_PROJ"

From this cell i need to substract the last part (after the last "/"') in this case: SET_PROJECT_SUB_PROJ
and the one before the last "/" in this case "METHOD" and store both in a different cell

is there a way to do this?

Thanks in advance.

 

[sheetspread]

Excel 2010
	A
1	T:\DEPLOY\NETHERLAND\SAPBEHEER\SOURCESPACKAGE\ZCB\ABAP/CLASSPOOL/ZCL_CB_PROJECT/CLASS/ZCL_CB_PROJECT/METHOD/SET_PROJECT_SUB_PROJ
2
3	SET_PROJECT_SUB_PROJ
4	METHOD
Sheet17

Cell Formulas
Range	Formula
A3	=TRIM(RIGHT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200),100))
A4	=TRIM(LEFT(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),200),100))

If method is supposed to appear first then it's even easier

 

[BVOPP]

Thanks this really works
Thanks a lot !!

 

[jtakw]

Hi,

I think sheetspread meant this for A3 formula:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),100))

 

[sheetspread]

Hi,

I think sheetspread meant this for A3 formula:

=TRIM(RIGHT(SUBSTITUTE(A1,"/",REPT(" ",100)),100))

Yes that's better than the redundant one I had