Another Thread extraction question!

[jcopen]

I want a formula that will take a filepath reference and yield only the filename, for example:

Cell A1 contains:

<TABLE style="WIDTH: 402pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=536 x:str><COLGROUP><COL style="WIDTH: 402pt; mso-width-source: userset; mso-width-alt: 19602" width=536><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 402pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17 width=536>C:\Documents and Settings\Joel\My Documents\My Music\From Smile\KotoStrum.L.wav</TD></TR></TBODY></TABLE>

I want a formula that yields:

KotoStrum.L.wav

What makes it more complex is that I want it to work on filepaths of various origins and depths. Essentially, I need something that would find the LAST occurrance of "\", then I could use any of several methods I already know to grab what follows that last occurrance. So far, I haven't found any trick that will find last occurrance or search from right to left, either of which would nail it for me. Any help would be much appreciated! Thank you.

 

[Sandeep Warrier]

I want a formula that will take a filepath reference and yield only the filename, for example:

Cell A1 contains:<table style="width: 402pt; border-collapse: collapse;" x:str="" width="536" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width: 402pt;" width="536"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(212, 208, 200); background-color: transparent; width: 402pt; height: 12.75pt;" width="536" height="17">C:\Documents and Settings\Joel\My Documents\My Music\From Smile\KotoStrum.L.wav

I want a formula that yields:

KotoStrum.L.wav

What makes it more complex is that I want it to work on filepaths of various origins and depths. Essentially, I need something that would find the LAST occurrance of "\", then I could use any of several methods I already know to grab what follows that last occurrance. So far, I haven't found any trick that will find last occurrance or search from right to left, either of which would nail it for me. Any help would be much appreciated! Thank you.

Hi,

Welcome to the Board....
Perhaps this will help...

Excel Workbook
	A	B
1	C:\Documents and Settings\Joel\My Documents\My Music\From Smile\KotoStrum.L.wav	KotoStrum.L.wav
Sheet3

Excel 2003

Cell Formulas
Range	Formula
B1	=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",255)),255))

 

[jcopen]

SanDeep,

Wow, that is really slick! Especially the 255 for the Windows file length limit. Thank you so much!

jcopen