XL: How to Display the File Name of a Worksheet Without Using a Macro

Article translations Article translations
Article ID: 214084 - View products that this article applies to.
This article was previously published under Q214084
For a Microsoft Excel for Macintosh version of this article, see 323225.
Expand all | Collapse all

SUMMARY

This article describes how to create a formula that displays the file name of a worksheet in a cell.

For example, for a file with the path C:\Excel\Data\Test.xls the formula returns Test.xls.

MORE INFORMATION

To display the name of the current worksheet, type the following formula as an array formula
   =RIGHT(CELL("filename"),LEN(CELL("filename"))-
   MAX(IF(NOT(ISERR(SEARCH("\",CELL("filename"),
   ROW(1:255)))),SEARCH("\",CELL("filename"),ROW(1:255)))))
				
where "filename" is literally the word "filename", in quotation marks.

NOTE: You must input this formula as an array formula. To input a formula as an array formula, press CTRL+SHIFT+ENTER.

The formula returns the full path and worksheet name of the current workbook as long as the workbook has been saved at least once. If you use this formula in an unsaved workbook, the formula cell remains blank until you save the workbook.

To return the name of the workbook only, use the following formula:
   =MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
   SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)
				
NOTE: You do not have to input this formula as an array formula.

If you use this formula on an unsaved worksheet, you receive the following error:
   #VALUE!
				
When you save the worksheet, the file name replaces the error.

Properties

Article ID: 214084 - Last Review: January 24, 2007 - Revision: 2.2
APPLIES TO
  • Microsoft Excel 2000 Standard Edition
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowto KB214084

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com