This Page: www.cpearson.com/Excel/CellWorksheetFunctions.aspx

Last Updated: 03-Sep-2014

Copyright 1997 - 2014, Charles H. Pearson

Site Last Updated: 28-Dec-2017

Advanced Software

Design And Development

Design And Development

Office Integration Projects
NET Programming
XML Development

Search The Site:

Introduction

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 CELL Function

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.

Full File Name

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

File Name Only

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

Sheet Name Only

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.

Path Name Only

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.

Parent Folder Only

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.

Drive Letter Only

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. |