Insert the Name of WorkBook in the Cell

Workbook Name is very rarely required value in excel But Workbook Name can be used in the formula like “Indirect” function to refer an internal or external address of any particular File.

Excel doesn't provide any inbuilt function to retrieve the filename in any cell. But it provides us a simple way to retrieve it through combination of formulas.

Here we illustrate how to retrieve the File Name

=SUBSTITUTE (LEFT (CELL ("FileName"), FIND ("]", CELL ("filename"))-1),"[","")

Here 4 function are used to achieve our goal

1. Substitute: this function simply finds a part of text in the complete sentence and replaces with another
like
=substitute ("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 of Characters from any text including spaces
Like
=Left("ABCD",2) *here we instructed excel to retrieve 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 sentence
=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 retrieves information from Excel Application and returns it to us.
There are several parameters provided to for different purposes. In our case we used it to retrieve 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

using the find function we retrieved the position of  "]"
from the Left to till the position ] we retrieved the text using left function
and using substitute function we replaced [ as "" (2 double quotes continuously denote 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 formula
C:\Amit\Office\BlogData.xls