Function: Filename

M

MarkC

Using Excel XP:

I went to INSERT>FUNCTION, clicked on CELL, click "ok" and typed the word
filename in the Info_Type field. This gave me the path with the filename,
but it also included the sheet name. I like to just have the Path and
Filename only, no sheet name, is it possible and if so how? Do I type
something in the Reference Field?

Just in case I need this later on, is it possible to just have the filename
by itself using the Function feature?

I also tried the Function INFO, and typed directory. Is there more info in
this area that's not mentioned that can be useful?

Thanks in advance

MarckC
 
A

Arvi Laanemets

Hi

To return the path only
=MID(CELL("filename";$A$1);1;FIND("[";CELL("filename";$A$1))-1)
To return the filename with extension only
=MID(CELL("filename";$A$1);FIND("[";CELL("filename";$A$1))+1;FIND("]";CELL("
filename";$A$1))-FIND("[";CELL("filename";$A$1))-1)
To return the sheet name you are referring to in formula
=MID(CELL("filename";$A$1);FIND("]";CELL("filename";$A$1))+1;35)
when referring to current sheet
=MID(CELL("filename";SheetName!$A$1);FIND("]";CELL("filename";SheetName!$A$1
))+1;35)
when referring to other sheet (the formula will adjust automatically,
whenever you rename the sheet)
 
M

MarkC

Thank You I will try it out.
m

Arvi Laanemets said:
Hi

To return the path only
=MID(CELL("filename";$A$1);1;FIND("[";CELL("filename";$A$1))-1)
To return the filename with extension only
=MID(CELL("filename";$A$1);FIND("[";CELL("filename";$A$1))+1;FIND("]";CELL("
filename";$A$1))-FIND("[";CELL("filename";$A$1))-1)
To return the sheet name you are referring to in formula
=MID(CELL("filename";$A$1);FIND("]";CELL("filename";$A$1))+1;35)
when referring to current sheet
=MID(CELL("filename";SheetName!$A$1);FIND("]";CELL("filename";SheetName!$A$1
))+1;35)
when referring to other sheet (the formula will adjust automatically,
whenever you rename the sheet)

--
When sending mail, use address arvil<at>tarkon.ee
Arvi Laanemets


MarkC said:
Using Excel XP:

I went to INSERT>FUNCTION, clicked on CELL, click "ok" and typed the word
filename in the Info_Type field. This gave me the path with the
filename,
but it also included the sheet name. I like to just have the Path and
Filename only, no sheet name, is it possible and if so how? Do I type
something in the Reference Field?

Just in case I need this later on, is it possible to just have the filename
by itself using the Function feature?

I also tried the Function INFO, and typed directory. Is there more info in
this area that's not mentioned that can be useful?

Thanks in advance

MarckC
Click to expand...
Click to expand...
 
Top