=cell("filename")

[Steve]

I am trying to use a function to auto add the filename to a cell. I went to
http://www.xldynamic.com/source/xld.xlFAQ0002.html for advice and got what i
was looking for. The only problem i am having is the extension is coming up
also. Is there to get rid of it? So if the filename is test.xls, I just want
test to show. I am using the function below.

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

Thanks,
Steve

[JE McGimpsey]

One way:

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

In article <D5CA46E2-E2A5-48D8-B511-63DD3EF908BE@microsoft.com>,
"Steve" <sgauer@neo.rr.com.nospamallowed> wrote:

> I am trying to use a function to auto add the filename to a cell. I went to
> http://www.xldynamic.com/source/xld.xlFAQ0002.html for advice and got what i
> was looking for. The only problem i am having is the extension is coming up
> also. Is there to get rid of it? So if the filename is test.xls, I just want
> test to show. I am using the function below.
>
> =MID(CELL("filename",A1),FIND("[",CELL("filename",A1),1)+1,FIND("]",CELL("file
> name",A1),1)-FIND("[",CELL("filename",A1),1)-1)
>
> Thanks,
> Steve

[Harlan Grove]

JE McGimpsey wrote...
>One way:
>
>=MID(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-5),
>FIND("[",CELL("filename",A1))+1,255)
....

Standard caveat: if the file is new and hasn't been saved, this doesn't
return anything useful because CELL("filename",A1) would return "".
Also, the formula above assumes extensions are always 3 characters. Not
unreasonable, but not general (e.g., *.html and, I believe, most Excel
files on Macs).

FWLIW, if there were a dummy worksheet named _ (a single underscore),
then the defined name FOO referring to the formula
=CELL("Address",_!$A$1) would return the pathname concatentated with
the worksheet and range address. Then the filename without extension
would be given by

=MID(LEFT(CA,FIND("]",CA)-1),FIND("[",CA)+1,FIND(".",CA&".",FIND("[",CA))-2)

in all worksheets other than _, and it works with any extension and in
as-yet unsaved files.

More caveats. Excel converts square brackets in filenames (allowed by
Windows even if not allowed by Excel) into parentheses, but square
brackets earlier in the full drive/directory path remain as-is, so all
these formulas could fubar on such full pathnames. Also, filenames can
contain multiple periods, but only the final (rightmost) one delimits
the extension. If the filename were like foo.bar.xls, these formulas
would truncate it to foo rather than foo.bar. Even in such cases it's
still possible to return the base filename, but the formulas become
HUGE, so better to use udfs written in VBA.


Function wbbn() As String
Dim n As Long
wbbn = Application.Caller.Parent.Parent.Name
n = Len(wbbn)
Do While n > 0
If Mid(wbbn, n, 1) = "." Then Exit Do
n = n - 1
Loop
If n > 0 Then wbbn = Left(wbbn, n - 1)
End Function

[JE McGimpsey]

Thanks, Harlan - I'd forgotten to add the standard caveat, and your
others are apropos, too, of course.

One last minor caveat for the VBA function - if a MacXL file were named,
say "My.File" without an extension, then wbbn's return would be
incorrect. I don't see any way to work around that other than having a
list of extensions that should be truncated.

That said, the default in MacXL now includes extensions, though they're
still not required.

In article <1127411988.339403.270650@f14g2000cwb.googlegroups.com>,
"Harlan Grove" <hrlngrv@aol.com> wrote:

> >One way:
> >
> >=MID(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-5),
> >FIND("[",CELL("filename",A1))+1,255)
> ...
>
> Standard caveat: if the file is new and hasn't been saved, this doesn't
> return anything useful because CELL("filename",A1) would return "".
> Also, the formula above assumes extensions are always 3 characters. Not
> unreasonable, but not general (e.g., *.html and, I believe, most Excel
> files on Macs).
>
> FWLIW, if there were a dummy worksheet named _ (a single underscore),
> then the defined name FOO referring to the formula
> =CELL("Address",_!$A$1) would return the pathname concatentated with
> the worksheet and range address. Then the filename without extension
> would be given by
>
> =MID(LEFT(CA,FIND("]",CA)-1),FIND("[",CA)+1,FIND(".",CA&".",FIND("[",CA))-2)
>
> in all worksheets other than _, and it works with any extension and in
> as-yet unsaved files.
>
> More caveats. Excel converts square brackets in filenames (allowed by
> Windows even if not allowed by Excel) into parentheses, but square
> brackets earlier in the full drive/directory path remain as-is, so all
> these formulas could fubar on such full pathnames. Also, filenames can
> contain multiple periods, but only the final (rightmost) one delimits
> the extension. If the filename were like foo.bar.xls, these formulas
> would truncate it to foo rather than foo.bar. Even in such cases it's
> still possible to return the base filename, but the formulas become
> HUGE, so better to use udfs written in VBA.
>
>
> Function wbbn() As String
> Dim n As Long
> wbbn = Application.Caller.Parent.Parent.Name
> n = Len(wbbn)
> Do While n > 0
> If Mid(wbbn, n, 1) = "." Then Exit Do
> n = n - 1
> Loop
> If n > 0 Then wbbn = Left(wbbn, n - 1)
> End Function