Excel provides only very limited support for determining with a formula the name of the worksheet, workbook, and
folder path. The one function it provides is the CELL function. However, with some
text manipulation, we can parse out information about the file name, folder name, sheet name, and drive letter.
The syntax of the CELL function is show below:
=CELL("filename",[reference])
In the CELL function, you must include "filename" as literal text as shown above. You
do not include the actual file name. The reference parameter is optional, but in
nearly all cases you want to use a cell on the same worksheet as the CELL function. The reference can
refer to any cell -- which cell is irrelevant. The parameter is used to determine what workbook and worksheet is used
and returned. If present, the worksheet and workbook containing the the range R
are used to generate the result. If omitted, the ActiveSheet is used when the formula is calculated. The ActiveSheet may
not be the sheet containing the CELL function when calculation occurs and misleading results may occur.
Unless you have a very good reason for omitting the reference parameter,
you should always include it. All of the example formulas below include the reference parameter.
The result of the CELL function is the workbook name and sheet name in the following example format:
C:\Folder\Folder2\[FileName.xlsx]Sheet1
With some text manipulation, we can get several pieces of information about the file name and the sheet name.
The workbook must have been saved to disc for these functions to work. They will not work on a new workbook that has
never been saved. The workbook may be in an unsaved state, but it must have been saved to disc at least once.
The reason that that the formulas are as long as they are is because the CELL function is called
several times within the function. It is not possible to call CELL once and reuse its result elsewhere
in the formula.
The following formula will return the full file name.
=SUBSTITUTE(LEFT(CELL("Filename",A1),FIND("]",
CELL("Filename",A1))-1),"[","")
Using the example file name above, this will return C:\Folder\Folder2\FileName.xlsx
This function will return only the file name, without the folder path information.
=MID(CELL("filename",A1),FIND("[",CELL("filename",A1))+1,FIND("]",
CELL("filename",A1))-FIND("[",CELL("filename",A1))-1)
Using the example file name above, this will return FileName.xlsx
This function will return the sheet name only:
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)
Using the example file name above, this will return Sheet1.
This function will return the complete path name of the file, without the file name or sheet name.
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-2)
Using the example file name above, this will return C:\Folder\Folder2.
These functions will return the name of the folder that contains the file, without any other path inforrmation. If you have a proper file name in cell D3, for example
C:\One\Two\Three\FileName.xls, you can use the following formula to get the containing (inner-most) folder name:
=LEFT(MID(SUBSTITUTE(D3,"\","#",LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))-1),FIND("#",SUBSTITUTE(D3,"\","#",LEN(D3)-
LEN(SUBSTITUTE(D3,"\",""))-1))+1,999),FIND("\",MID(SUBSTITUTE(D3,"\","#",LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))-1),
FIND("#",SUBSTITUTE(D3,"\","#",LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))-1))+1,999))-1)
If cell D3 contains C:\One\Two\Three\FileName.xls, the formula will return Three. This formula will work
as long as the file is not in the root directory of a drive (e.g., F:\MyFile.xls). If it is possible that the file is directly in
the root directory of a drive, you need to expand the formula to:
=IF(LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))=1,LEFT(D3,FIND("\",D3)-1),LEFT(MID(SUBSTITUTE(D3,"\","#",LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))-1),
FIND("#",SUBSTITUTE(D3,"\","#",LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))-1))+1,999),FIND("\",MID(SUBSTITUTE(D3,"\","#",LEN(D3)-
LEN(SUBSTITUTE(D3,"\",""))-1),FIND("#",SUBSTITUTE(D3,"\","#",LEN(D3)-LEN(SUBSTITUTE(D3,"\",""))-1))+1,999))-1))
If you are using =CELL("filename",A1) to get the file name of the file that contains this formula, you can use the following formula:
=MID(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1),"\","#",LEN(LEFT(CELL("filename",A1),
FIND("\[",CELL("filename",A1))-1))-LEN(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1),"\",""))),
FIND("#",SUBSTITUTE(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1),"\","#",
LEN(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1))-LEN(SUBSTITUTE(LEFT(CELL("filename",A1),
FIND("\[",CELL("filename",A1))-1),"\",""))))+1,9999)
This formula will work as long as the file is not directly under the root drive (e.g., F:\MyFile.xls). If the file might be under the
root drive, you have to expand the formula to:
=IF(LEN(CELL("filename",A1))-LEN(SUBSTITUTE(CELL("filename",A1),"\",""))=1,LEFT(CELL("filename",A1),FIND("\",CELL("filename",A1))),
MID(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1),"\","#",LEN(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1))-
LEN(SUBSTITUTE(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1),"\",""))),FIND("#",SUBSTITUTE(LEFT(CELL("filename",A1),
FIND("\[",CELL("filename",A1))-1),"\","#",LEN(LEFT(CELL("filename",A1),FIND("\[",CELL("filename",A1))-1))-LEN(SUBSTITUTE(LEFT(CELL("filename",A1),
FIND("\[",CELL("filename",A1))-1),"\",""))))+1,9999))
The reason these formulas are so long is that there is no worksheet function to search text right-to-left. All of Excel's text functions
operate left-to-right. Another reason is that there is no single function to count the number of occurrances of a specific character within a
text string. Without these limitations, the formulas would be much simpler and shorter.
This function will return the drive letter (including the ':' character of the file.
=LEFT(CELL("filename",A1),FIND(":",CELL("filename",A1)))
Using the example file name above, this will return C:. This will support drives with a multi-letter
name (e.g., AB:). While this condition is very rare, the formula can support it. (I once new a guy
who partitioned a hard drive into 30 partitions just to see how the drive letters behave.)
 |
This page last updated: 17-August-2012. |