XL: Macroless Method to Display the File Name of a Worksheet

ID: Q99348

The information in this article applies to:

SUMMARY

In Microsoft Excel, if you want to create a formula that displays the file name of a worksheet in a cell but you do not want to use a macro, use the appropriate following method.

For example, to return the file name of a file with the path C:\EXCEL\DATA\TEST.XLS (if you are using Microsoft Excel for Windows) or HD:Microsoft Excel:Data Files:Test (if you are using Microsoft Excel for the Macintosh), the following formulas return TEST.XLS or Test.

If you are using Microsoft Excel 5.0 or later, the formulas in this article


MORE INFORMATION

Microsoft Excel for Windows or Microsoft Excel for OS/2

To display the name of the current worksheet, enter 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: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

The formula returns the name of the worksheet as long as the worksheet has been saved at least once. If you use this formula on an unsaved worksheet, you receive a #VALUE! error value.

In Microsoft Excel 5.0 or later, the formulas above will return the name of both the workbook and the active sheet. To return the name of the file only, use the following formula: 


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


Microsoft Excel for the Macintosh

To display the name of the current worksheet, enter the following formula as an array 


   =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: The above formula must be entered as an array formula. To enter a formula as an array formula in Microsoft Excel for Windows, press CTRL+SHIFT+ENTER. In Microsoft Excel for the Macintosh, press COMMAND+ENTER.

In Microsoft Excel 5.0 or later, the formulas above will return the name of both the workbook and the active sheet. To return the name of the file only, use the following formula: 


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


REFERENCES

"Function Reference," version 4.0, Pages 46, 239, 252, 377
"Function Reference," version 3.0, Pages 26, 137, 200, 208

Additional query words: XL98 XL97 XL7 XL5 XL4 XL3 macrosheet template howto 


Keywords          : xlformula 
Version           : WINDOWS:3.0,4.0,4.0a,5.0,5.0c,7.0,97; MACINTOSH:3.0,4.0,4.0a,5.0,98
Platform          : MACINTOSH OS/2 WINDOWS 
Issue type        : kbinfo

Last Reviewed: April 6, 1999