File Path into Single Cells

[How_Do_I]

Can anyone help me with this please:

I'm struggling as what I have so far isn't working, I can't extract the file name. Also, what I have isn't going to be robust in that it won't deal with folders that have more sub folders.

Excel Workbook
	C	D	E	F	G	H
31	xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf	4	xxx Old Laptop	WW I June 2016	Australian War	xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf
Sheet1

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C1</th><td style="text-align:left">=TRIM(<font color="Blue">MID(<font color="Red">SUBSTITUTE(<font color="Green">$A1,"",REPT(<font color="Purple">" ",100</font>)</font>),COLUMNS(<font color="Green">$C:C</font>)*99-98,99</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2010
	A	B	C	D	E	F
1	xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf		xxx Old Laptop	WW I June 2016	Australian War Diaries	RCDIG1003248.pdf
Sheet20

 

[How_Do_I]

Hi VBA Geek... I'm not sure what is going wrong here:-

Excel Workbook
	A	B	C
1	xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf		xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf
Sheet1

 

[AlanY]

there is a \ missing in the formula

=TRIM(MID(SUBSTITUTE($A1,[COLOR="#FF0000"]"\"[/COLOR],REPT(" ",100)),COLUMNS($C:C)*99-98,99))

if the filename only needed, you can try this

=TRIM(RIGHT(SUBSTITUTE(A1,"\",REPT(" ",500)),500))

 

[VBA Geek]

my formula actually was: =TRIM(MID(SUBSTITUTE($A1,"\",REPT(" ",100)),COLUMNS($C:C)*99-98,99))

when using the html add-in the backlash was removed

Hi VBA Geek... I'm not sure what is going wrong here:-

Sheet1

	A	B	C
1	xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf		xxx Old Laptop\WW I June 2016\Australian War Diaries\RCDIG1003248.pdf

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C1 =TRIM(MID(SUBSTITUTE($A1,"",REPT(" ",100)),COLUMNS($C:C)*99-98,99)) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[njimack]

C1: =left(a1,find("",a1)-1)
d1: =mid(a1,len(c1)+2,len(c1))
e1: =mid(a1,len(c1)+len(d1)+3,find("#",substitute(a1,"","#",len(a1)-len(substitute(a1,"",""))))-len(c1)-len(d1)-3)
f1: =mid(a1,len(c1)+len(d1)+len(e1)+4,find(".",a1)-len(c1)-len(d1)-len(e1)-4)

 

[How_Do_I]

That's got it... thanks VBA Geek and thank you too Alan.