I know that I can enter a cell file name with the following typed into the
cell:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

But, can I do this without showing the file extension (.xls)?

Maybe something like this?:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH(".xl?]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Steve" wrote:

I know that I can enter a cell file name with the following typed into the
cell:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

But, can I do this without showing the file extension (.xls)?

Thanks, Ron, it worked! I also found that this works as well (removes last 4
digits by changing -1 to -5, but I like your solution better:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-5)

"Ron Coderre" wrote:

Maybe something like this?:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH(".xl?]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Steve" wrote:

I know that I can enter a cell file name with the following typed into the
cell:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

But, can I do this without showing the file extension (.xls)?

Hello,
I realise that this is by no means a current thread but having just tried it out I thought I should feed back my findings:
If you have two worksheets open that both use any of these formulas the first one opened will display correctly until the second one is opened, whereupon it will display the filename of the second file. Pressing F9 to recalculate will result in both worksheets displaying the filename of the currently active sheet.
The formula is great, but is there anyway around the above little problem?

Kind regards
Benjamin

OK I'm now replying to my own posts...

Actually one of the formulas was different. The ones that use FIND work ok, the ones that use SEARCH are problematic with two or more sheets. This is due to the ones that use FIND referring to a cell in that worksheet. For an explanation of this see here;

http://www.xldynamic.com/source/xld.xlFAQ0002.html

So I now have two formulas:

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

or one from here
http://www.excelbanter.com/showthread.php?t=153010
=MID(CELL("FILENAME",A1),FIND("[",CELL("FILENAME",A1))+1,FIND("]",CELL("FILENAME",A1))-FIND("[",CELL("FILENAME",A1))-5)

For me they both work, but which one is more elegant?

Many thanks

Benjamin

HI SIR,

# CAN POSSIBLE IT IN VB?
# CAN I SOWN ONLY FIRST 6 DIGIT OF FILE NAME?

One way:

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

In article

,
Steve wrote:

I know that I can enter a cell file name with the following typed into the
cell:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

But, can I do this without showing the file extension (.xls)?

This formula uses the LEFT function to extract the characters from the left of the file name until the first period (.) is encountered, which is where the file extension starts. By doing this, the file extension is excluded from the displayed file name.