Another Thread extraction question!

jcopen

New Member
Joined
Aug 1, 2009
Messages
6
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
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
AB
1C:\Documents and Settings\Joel\My Documents\My Music\From Smile\KotoStrum.L.wavKotoStrum.L.wav
Sheet3
Excel 2003
Cell Formulas
RangeFormula
B1=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",255)),255))
 
Last edited:
Upvote 0
SanDeep,

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

jcopen
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top