=CELL("filename")

[P Rena 29]

Hi. I entered the above into my cell and it returns the path and filename AND
worksheet name so that I get a result that looks like this:

X:\YEAR 2000\XYZ\[JOHN4.XLS]SHEET 1

Does anyone out there know what function I can use to return ONLY the file name
JOHN4.XLS without all the other info?? Please respond and email me if you can?

"If we practice an eye for an eye and a tooth for a tooth, soon the whole world
will be blind and toothless." - Mahatma Gandhi

P. Rena - to reply to me by email please use PRE...@AOL.COM

[John Turner]

Isn't this a basic footer option?/ =Cell is in your worksheet whcih isn't
nery flexible if you change the worksheet later. Footer on page setup has
option for tab only.

[Chip Pearson]

This is one of my "canned" replies.....

Use
=CELL("filename",A1)
to get the full name, including the sheet name. E.g.,
C:\Temp\[Test.Xls]Sheet1

Use
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,LEN(CELL("fil
ename"
,A1))-FIND("]",CELL("filename",A1)))
to get just the sheet name
Sheet1

Use
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",CELL
("file
name",A1))-FIND("[",CELL("filename",A1))-1)
to get just the file name
Test.Xls

Use
=LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1)))
to get full name without sheet name
C:\Temp\[Test.xls]

Use
=SUBSTITUTE(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filenam
e",A1)
)),"[",""),"]","")
to get full name without sheet name, and without square brackets
C:\Temp\Test.Xls

Cordially,
Chip Pearson
http://home.gvi.net/~cpearson/excel.htm

P Rena 29 wrote in message
<19990121152930...@ng62.aol.com>...

[Chip Pearson]

[Bill Manville]

If you want it in a single formula:

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

name",A1))-FIND("[",CELL("filename",A1))-1)

Or, if you have =CELL("filename",A1) in say B1 then
=MID(B1,FIND("[",B1)+1,FIND("]",B1)-FIND("[",B1)-1)

Bill Manville
MVP - Microsoft Excel
Oxford, England

P Rena 29 <pre...@aol.comNOJUNK> wrote in message
19990121152930...@ng62.aol.com...

[John Stephens]

On 21 Jan 1999 20:29:30 GMT, pre...@aol.comNOJUNK (P Rena 29) wrote:

>Hi. I entered the above into my cell and it returns the path and filename AND
>worksheet name so that I get a result that looks like this:
>
>X:\YEAR 2000\XYZ\[JOHN4.XLS]SHEET 1
>
>Does anyone out there know what function I can use to return ONLY the file name
>JOHN4.XLS without all the other info?? Please respond and email me if you can?
>
>"If we practice an eye for an eye and a tooth for a tooth, soon the whole world
>will be blind and toothless." - Mahatma Gandhi
>
>P. Rena - to reply to me by email please use PRE...@AOL.COM

From the format used to display this information, the filename is enclosed
in square brackets.

Use the text functions FING and MID to extract the wanted portion of the
full path/file/tab name.

If the CELL function is in A1, then you can get the filename by using:
=MID(A1,FIND("[",A1,1)+1,FIND("]",A1,1)-FIND("[",A1,1)-1)

--
John Stephens (remove spaces for legal email address)
s t e p h e n s @ p o b o x . c o m

[Leo Heuser]

Also you could use a UDF like this:

1. Go to the VBA-editor with <Alt><F11>
2. Doubleclick the name of the workbook in the project window (upper left of the screen)
3. Choose Insert > Module
4. Enter this function.

Function fname(nName As String)
Select Case LCase(nName)
Case "pathfile"
fname = ActiveWorkbook.FullName
Case "path"
fname = ActiveWorkbook.Path
Case "file"
fname = ActiveWorkbook.Name
Case "sheet"
fname = ActiveSheet.Name
Case Else
fname = "Not defined"
End Select
End Function

In the sheet you could enter:

=fname("pathfile") to get D:\Data\Excel\Budget.xls
=fname("path") to get D:\Data\Excel
=fname("file") to get Budget.xls
=fnavn("sheet") to get Sheet1 (or whatever)

Best regards
LeoH

P Rena 29 skrev i meddelelsen <19990121152930...@ng62.aol.com>...