Use Of FILENAME

Use Of FILENAME

There may be times when you need to insert the name of the current workbook
or worksheet in to a cell.


This can be done by using the CELL() function, shown below.
C:\Users\Nitin Pal Singh\Desktop\[Excel Master Sheet.xlsb]Home
=CELL("filename")


The problem with this is that it gives the complete path including drive letter and folders.
To just pick out the workbook or worksheet name you need to use text functions.


To pick the Path.
C:\Users\Nitin Pal Singh\Desktop\
=MID(CELL("filename"),1,FIND("[",CELL("filename"))-1)


To pick the Workbook name.
Excel Master Sheet.xlsb
=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-1)


To pick the Worksheet name.
Home
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,255)

                             My stories                       Index           daily thoughts