Formula to find filename

[Guest]

Hi

I have a written the file path of File A in a cell of another spreadsheet,
say File B. The file path would be written as below
C:\MY Files\Media\Test.doc

I need a excel cell formula which can return the name of the
file (ie "Test.doc") no matter how many directories or the lenght of the
address. Note Cell("filename",a1) does not help me because this returns the
path of the existing spredsheet. Seems I need a way to find where the last
"\" is in the file path

any ideas

thanks

 

[Guest]

=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)

 

[Guest]

This is tough to do with a worksheet function because it is possible that
folders in the path could also contain the . symbol as part of their name,
along with the multiple \ symbols you expect.

But if you'll revisit that other workbook and put the entries (I presume
there's going to be more than one?) into the format returned by the CELL
function, that is, as:
C:\MY Files\Media\[Test.doc]
then you can modify Gary's offered formula to something like this (remember,
one long entry - not actually broken up as the editor here is no doubt going
to do)

=MID('[File B.xls]OtherWBSheet'!$A$1,FIND'[File
B.xls]OtherWBSheet'!$A$1)+1,FIND("]",'[File
B.xls]OtherWBSheet'!$A$1)-FIND("[",'[File B.xls]OtherWBSheet'!$A$1)-1)

If you need to keep the entries in File B in their current form (no
brackets), then add another column with them in that format for use in this
workbook to find them?

------------
Another option would be to use a User Defined Function that uses the VB
InstrRev() function to extract the right portion beyond the last \ symbol.

The function would look like this:
Function GetForeignFilename(anyPath As String) As String
GetForeignFilename = Right(anyPath, Len(anyPath) - _
InStrRev(anyPath, "\"))
End Function

and in your worksheet you would call it as:
=GetForeignFilename('[File B.xls]OtherWBSheet'!$A$1)