Remove path from file name

[dantb]

Hello all: Is there a formula where only the data after the second or third \ be shown in a cell no mater what the path is? Thanks Dan

D:\A Print\D72PI010-R00.PDF
D:\A Print\Temp\D72PI010-R00.PDF

Formula in both cases would return just the file name D72PI010-R00.PDF

 

[dantb]

Thanks Jon: I understand what it is doing now, I modified the formula using * and the % sign and the formula still worked,thanks for explaining. Interesting to know for future reference. Thanks again Dan

 

[kcmuppet]

=REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),"")

Hi, is there a way of making this work to also remove the [ ] and sheet name from the result of a =cell("filename")?

...so that it can be referenced in a hyperlink formula (ie. always use the current filename in the formula)?

 

[just_jon]

=REPLACE(A1,1,FIND("@",SUBSTITUTE(A1,"\","@",LEN(A1)-LEN(SUBSTITUTE(A1,"\","")))),"")

Hi, is there a way of making this work to also remove the [ ] and sheet name from the result of a =cell("filename")?

...so that it can be referenced in a hyperlink formula (ie. always use the current filename in the formula)?

=SUBSTITUTE(LEFT(A1,FIND("]",A1)-1),"[","")

Where A1 holds the full path/file/sheetname.

Or, all in one

=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

 

[kcmuppet]

Thanks Jon, I'm struggling to combine them though.

I've got...
=CELL("filename") in D2...

and...
=REPLACE(D2,1,FIND("@",SUBSTITUTE(D2,"\","@",LEN(D2)-LEN(SUBSTITUTE(D2,"\","")))),"") in D3...

and..
=SUBSTITUTE(LEFT(D3,FIND("]",D3)-1),"[","") in d4

D4 is the filename without the path and sheetname, which I'm actually trying to put into the blue coloured part of the formula below:

=HYPERLINK("[here]"&ADDRESS(MATCH(A11,'Bank Statements'!A:A,FALSE),1,4,1,"bank statements"),"Click to see it...")

Ideally, all in the hyperlink formula.

I also tried using:
"["&MID(CELL("filename",D2),(FIND("[",CELL("filename",D2))+1),(FIND(".xls",CELL("filename",D2)))-(FIND("[",CELL("filename",D2))+1))&"]"

...which does a nice job of returning just [filename], but then I can't work the right syntax to put that into the hyperlink formula.

(I can't believe excel doesn't have simple way to either reference a filename without a path or have hyperlinks cope with whatever the active workbook is called, if the link stays within that workbook. - This all seems rather complicated )

 

[kcmuppet]

I'm spending hours getting nowhere. Could somebody tell me why this doesnt work:

If this exists as text in cell A1 on Sheet 2...

[book1]Sheet1!B1

...then why doesn't this in cell A2 on Sheet 2...

=HYPERLINK(""""&A1&"""")

...work as a hyperlink?

I get the cannot open the specified file message

 

[fairwinds]

Why all the quotes?

Try:

=HYPERLINK(A1)

 

[kcmuppet]

Why all the quotes?

Try:

=HYPERLINK(A1)

Because it doesn't work without quotes, and because Excel's Help shows the syntax with quotes it (See below), so to get the quotes around it, apparently you need to put four quotes in each side - but that doesnt work either, so I'ma bit stuck. Surely others must have found a way to have a hyperlink formula contain automatically contain the current filename without all this difficulty?

You can create hyperlinks within a worksheet to jump from one cell to another cell. For example, if the active worksheet is the sheet named June in the workbook named Budget, the following formula creates a hyperlink to cell E56. The link text itself is the value in cell E56.

=HYPERLINK("[Budget]June!E56", E56)

 

[kcmuppet]

Solved

Solved! - I found thison OzGrid

I knew there had to be a simpler way. You can just use a hash, like this...

=hyperlink("#Sheet1!B1")

...instead of trying to extract the current filename and reference it in the hyperlink formula or enter it straight in.

So where is that explained in the online manual?