How do you insert the current file name in Excel?
It works here, I just tested on Excel 2007 (XP) and Excel 2013 (Windows 8).
Try =CELL("filename") alone first to help troubleshoot.
Make sure you have saved your workbook to disk. It will not show "Book1" if you put the formula in a new unsaved workbook.
Are you using non-english regional settings on your computer? Some languages requiring adjusting function names and formula syntax (in French for instance, commas must be replaced by semicolons).
Are you on your personal computer or a workplace computer? If it's a work computer, there could perhaps be user rights limitations causing issues.
When you say "it doesn't work", can you be more specific? Does it accept the formula as typed? Is the cell empty, showing "#VALUE!", etc? What happens when just using =CELL("filename")?
Note that =CELL("filename") gives you the path to the last workbook/worksheet changed so if you have 2 workbooks open, abc and xyz and your formula is in abc, if you last changed a value in xyz the formula in abc will reflect that and return xyz.xlsx
For that reason it's usually preferable to include a cell reference (any cell reference), e.g.
=CELL("filename",A1)
Using that version means that you will only ever get the workbook name of the workbook in which the formula resides.
You can use this slightly shorter version
=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,FIND("[",CELL("filename",A1)),"")
I've found this to be the "simplest" formula to return the filename only:
=REPLACE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),1,FIND("[",CELL("filename",A1)),"")
or - created in VBA:
Formula = "=REPLACE(LEFT(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))-1),1,FIND(""["",CELL(""filename"",A1)),"""")"
To return the full path, this will do:
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")
or - created in VBA:
Formula = "=SUBSTITUTE(LEFT(CELL(""filename"",A1),FIND(""]"",CELL(""filename"",A1))-1),""["","""")"