RE: Formula to find filename

Giganews Newsgroups
Subject: RE: Formula to find filename
Posted by:  JLatham
Date: Sun, 5 Aug 2007

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)-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)

"Kevin" wrote:

> 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
> --
> Kevin



In response to

Formula to find filename posted by Kevin on Sun, 5 Aug 2007