14

I am trying to view the file name that I am working on in the Excel spreadsheet of interest using a formula. For example if the file was called workbook1.xlsx then I would want cell A1 to show "workbook1.xlsx"

The formula that Microsoft's help site gives doesn't work: http://office.microsoft.com/en-us/excel-help/insert-the-current-excel-file-name-path-or-worksheet-in-a-cell-HA010103010.aspx#BMinsert3

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

  • In what way does it not work? What output does it give? Did you save the workbook? – user273580 Jun 10 '13 at 20:52
14

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")?

  • I think I had a space or something, it works now. Thanks! – timothy.s.lau Jun 10 '13 at 20:58
  • Doesn't seem to work in the Excel 2015 beta. – Akku Apr 13 '15 at 13:55
  • Make sure the markup of the cell that you are in is general / standard (so not text/number, etc) – Lexib0y Jan 22 '16 at 13:25
  • 1
    It didn't work for me, until I changed the comma character to semi-colon, according to my locale. – mavrosxristoforos Jan 4 '18 at 17:41
9

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)),"")

2

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),""["","""")"
1

I had the same problem (the pasted formula didn't work)! Once I replaced the , by ; in the formula it worked perfect.

=MID(CELL("filename");SEARCH("[";CELL("filename"))+1; SEARCH("]";CELL("filename"))-SEARCH("[";CELL("filename"))-1)

  • I believe that this is because you are using a Euro version of Excel. The Euro version uses semicolons instead of commas for function parameters. International version use commas as well, I believe. – krowe2 Jan 9 '15 at 17:34
  • It's not really a matter of what version of excel you're running but rather what 'delimiter' you're using in the regional settings. – deroby Jul 15 '15 at 12:47
1

More simple solution is this:

In your Excel file press Alt+F11, which opens Microsoft Visual Basics for Applications (VBA). In VBA select Insert > Module and paste the following code

Public Function GetMyProp(prop As String) As String
    GetMyProp = ThisWorkbook.BuiltinDocumentProperties(prop)
End Function

In your Excel file type formula: =GetMyProp("Title"). This will show the title of your document in the chosen cell.

1

If you use excel in Dutch, you should use the Dutch function names and use semicolons instead of comma:

=VERVANGEN(LINKS(CEL("filename");VIND.SPEC("]";CEL("filename"))-1);1;VIND.SPEC("[";CEL("filename"));"")

protected by Ben N Oct 28 '17 at 17:01

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.