8. Excel Tips - Get file path with an excel formula

Nick's picture


This is a quirky one.


You can use the excel function call CELL("filename") to retrieve something that should be the file name, but for some odd reason isn't.


Fortunately, the function call does have a couple of uses - You can use it to retrieve the file path by using a few text functions around it like so: =LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)


- returns file path (if you have saved the file).


Try it and see !

Also take a look at how to permanently show the file path on the toolbar:

excelexperts.com/Excel-Tips-Display-Excel-File-Path


Training Video on getting the file path:



Get the full pathed file name

You can however, replace th "[" with "]" and remove the -1 at the end:=LEFT(CELL("filename"),FIND("]",CELL("filename"),1))

and get the full path name and file name:S:\BofARpt\CashReport\[0510_CashRpt.xlsm]

insterting the full path and file name in excel

This is exactly what I was looking for, thank you!

thanks

exactly what I was today looking for!

Help

Ok I tried putting in my filename is all of above and nothing

G:\PHOTOBIO\Patrick\test\[CALFACT.xls]CalFac!$F3

so I have =LEFT(CELL("G:\PHOTOBIO\Patrick\test\[CALFACT.xls]CalFac!$F3"),FIND("]",CELL("G:\PHOTOBIO\Patrick\test\[CALFACT.xls]CalFac!$F3"),1))

triple checked it.

it just comes up value....basically i want to set up a macro that will grab this column then multiply it with another column in current worksheet ...any ideas as at wits end here .

thanks
P

Nick's picture

FileName

in:  CELL("filename")

"filename"  is exactly what you input to the function. Not your actual filename. Excel will calculate the name of the file.

 

NOTE: You can get the workbook name with this formula:

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

=>

ABC.xls

 

 

 

Get file path

Dear Nick,
Thank you for this really useful tip.
Because I'm curious, I tried both functions.
<<=CELL("filename")>> actually produces the full file-path e.g.
C:\***MESTUFF\**FILES\***MENT\ACCOUNTS\[2009Deposits Cheques Summ 1Sheet.xlsx]1SHEET,
which is exactly what was wanted.

<<=LEFT(CELL("filename"),FIND("[",CELL("filename"),1)-1)>> produces
C:\***MESTUFF\***FILES\***MENT\ACCOUNTS\.

Could this be a characteristic of Excel 2007 under XP

Thanks again!
gaveldot