Microsoft KB Archive/99348

{|
 * width="100%"|

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

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a, 5.0, 5.0c
 * Microsoft Excel for Windows 95, versions 7.0, 7.0a
 * Microsoft Excel 97 for Windows
 * Microsoft Excel for OS/2, version 3.0
 * Microsoft Excel for the Macintosh, versions 3.0, 4.0, 5.0, 5.0a
 * Microsoft Excel 98 Macintosh Edition

-

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

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(&quot;filename&quot;),LEN(CELL(&quot;filename&quot;))-

MAX(IF(NOT(ISERR(SEARCH(&quot;\&quot;,CELL(&quot;filename&quot;),     ROW(1:255)))),SEARCH(&quot;\&quot;,CELL(&quot;filename&quot;),ROW(1:255)))))

where &quot;filename&quot; is literally the word &quot;filename&quot;, 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(&quot;filename&quot;),SEARCH(&quot;[&quot;,CELL(&quot;filename&quot;))+1,SEARCH(&quot;]&quot;,CELL (&quot;filename&quot;))-SEARCH(&quot;[&quot;,CELL(&quot;filename&quot;))-1)

Microsoft Excel for the Macintosh
To display the name of the current worksheet, enter the following formula as an array

  =RIGHT(CELL(&quot;filename&quot;),LEN(CELL(&quot;filename&quot;))-

MAX(IF(NOT(ISERR(SEARCH(&quot;:&quot;,CELL(&quot;filename&quot;),     ROW(1:255)))),SEARCH(&quot;:&quot;,CELL(&quot;filename&quot;),ROW(1:255)))))

where &quot;filename&quot; is literally the word &quot;filename&quot; 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(&quot;filename&quot;),SEARCH(&quot;[&quot;,CELL(&quot;filename&quot;))+1,SEARCH(&quot;]&quot;,CELL (&quot;filename&quot;))-SEARCH(&quot;[&quot;,CELL(&quot;filename&quot;))-1)