Extract a file name from a full (variable) file path

[P0lar]

I have a list of xml command lines in excel (text fields) such as:

-f /tv/ss/ex/dub/config/agedet_one2one_run.xml -s<s> -e<e> > /tv/ss/ex/dub/config/run_agedet.log 2>&1

-f /tv/ss/in/au/config/agedet_out_run.xml -s<s> -e<e> > /tv/ss/ex/dub/config/run_agedet.log 2>&1

-f /tv/ss/ex/con/agedet_team_run.xml -s<s> -e<e> > /tv/ss/ex/dub/con/run_agedet.log 2>&1

and I need to extract the filenames which are immediately before the .xml part in all cases (so the agedet_one2one_run.xml part for example). The filepaths are always slightly different and there can be different numbers of folders in the string. The filenames always end .xml -s.

I'm looking for a text formula which will return the text string between the last "/" in the first path of the string and ".xml" (the bit hopefully in blue)

 

[tomas]

Hi

try formula in the attachment

 

[Khalid NGO]

Hi,

Please upload your sample file.

meanwhile check this:
=SUBSTITUTE(SUBSTITUTE(A1,MID(A1,FIND("/",A1)-3,FIND(CHAR(135),SUBSTITUTE(A1,"/",CHAR(135),6))),""),MID(A1,FIND(".xml",A1),99),"")

Refer to:
http://chandoo.org/wp/2012/05/17/formula-forensic-no-021/

Regards,

 

[Khalid NGO]

Oops..! didn't checked the last string , here is the revised one, but little long:

=SUBSTITUTE(SUBSTITUTE(A1,MID(A1,FIND("/",A1)-3,FIND(CHAR(135),SUBSTITUTE(A1,"/",CHAR(135),IF(LEN(MID(A1,FIND("/",A1)-3,FIND(CHAR(135),SUBSTITUTE(A1,"/",CHAR(135),6))))<30,6,5)))),""),MID(A1,FIND(".xml",A1),99),"")

Regards,

 

[P0lar]

Thanks for the pointers, got most of the records working now using the following monster... had to adjust as the number of directories in the path can vary and the string contains two paths, one of which isn't needed...

=MID(SUBSTITUTE(LEFT(A1,FIND(">",A1)),"/",CHAR(135),LEN(LEFT(A1,FIND(">",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(">",A1)),"/",""))),FIND(CHAR(135),SUBSTITUTE(LEFT(A1,FIND(">",A1)),"/",CHAR(135),LEN(LEFT(A1,FIND(">",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(">",A1)),"/","")))),FIND(" ",SUBSTITUTE(LEFT(A1,FIND(">",A1)),"/",CHAR(135),LEN(LEFT(A1,FIND(">",A1)))-LEN(SUBSTITUTE(LEFT(A1,FIND(">",A1)),"/",""))),32))