This is very rarely required value in excel but Essencial. this can be used in the formulas to refer the exact location of any file.
Excel doesn't provide direct inbuild feature to retrive the filename in any cell. But it provids us a simple way to retrive it through comination of formulas.
Use the following formula:
=SUBSTITUTE(LEFT(CELL("FileName"),FIND("]",CELL("filename"))-1),"[","")
Here we have used four function to achive our goal
1. sustitute: this function simply finds a part of text in the complete sentencd and replaces with another
like
=sustitute("ABCD","A","X")
In above example from "ABCD" the "A" will be replaced by "X", and the return value of the formula will be XBCD
2. Left: This simply return the leftmost Nth numbers from any text
Like
=Left("ABCD",2) *here we instructed excel to retrive only 2 left characters from ABCD
Thus formula will return - AB
3. Find: this function will simply give the Nth position of the searched text from sentance
=Find("B","ABCD") this function will return the position of B from the left so the value will be 2, as B is on 2nd position from left.
4. Cell: This function retrives the information from Excel Application and return it to us.
there are several perameters provided to for different purposes. in our case we used it to retrive the Name of file.
=Cell("FileName")
this will return the Location + Name of File (Enclosed within Square Brackets) + SheetName
like
C:\Amit\Office\[BlogData.xls]sheet1
useing the find function we retrived the position of ]
from the Left to till the position ] we retrived the text using left function
and using substitute function we replaced [ as "" (2 double quotes continously denot the blank) from the returned value.
we can use this formula in any cell and we can have the name of file in the cell we enter the foumula
C:\Amit\Office\BlogData.xls
