ThreeWave Returning The File Or Folder Name Of A Worksheet To A Cell

This page describes formulas for returning file and folder names in Excel cells.
ShortFadeBar

Introduction

It may come as a surprise that Excel doesn't provide built-in functions for getting the name of the current workbook or worksheet. Such functions would be a welcome improvement to Excel's arsenal. Excel does provide one function, CELL, from which can be extracted the file name, folder name, sheet name, and file extension. The basic formula is 

=CELL("FileName",A1)

In this formula, you use the text FileName exactly as show; you do not substitute the actual file name. The reference to A1 can refer to any cell on the worksheet. If that parameter is omitted, the result of the function may be incorrect. The CELL function returns a string in the following format: C:\Test\[WorkbookName.xlsm]SheetName. The sheet name returned in the string is the name of the sheet on which the cell in the second parameter resides. If this second parameter is omitted, the function returns the name of the active sheet, which is not necessarily the sheet on which the formula resides. The CELL function looks at the second parameter to get the file and sheet name. If this parameter is omitted, the active workbook and active worksheet at the time of calculation are used. So, for example, if a cell on Sheet1 contains the formula =CELL("FileName") and Sheet2 is the active sheet when Excel calculates the workbook, the formula on Sheet1 will return the name of Sheet2 because Sheet2 was active when the formula was calculated. This can lead to errors in other formulas and general confusion. The second parameter to the CELL function locks the function to the appropriate worksheet.

NOTE: The formulas below will work only if the workbook has been saved to disk. They will not work on a new, unsaved file since that file does not have a folder path.

Extracting The Full File Name

You can use the following formula to get the full file name of the workbook. 

=SUBSTITUTE( LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1))-1),"[","")

If you prefer a VBA function, use the following: 

Function WorkbookFullName(R As Range) As String
    WorkbookFullName = R.Worksheet.Parent.FullName
End Function

Extracting The File Name

You can get just the file name, without the folder path information, with the following formula: 

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

If you prefer a VBA functions, use the following: 

Function FileName(R As Range) As String
    FileName = R.Worksheet.Parent.Name
End Function

Extracting The Sheet Name

You can get the worksheet name with the following formula: 

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,99)

If you prefer a VBA function, use the following: 

Function SheetName(R As Range) As String
    SheetName = R.Worksheet.Name
End Function

Extracting The Folder Name

You can get the folder name with the following formula: 

=LEFT(CELL("Filename",A1),FIND("[",CELL("Filename",A1))-2)

If you prefer a VBA function, use the following: 

Function PathName(R As Range) As String
    PathName = R.Worksheet.Parent.Path
End Function

Extracting The File Extension

You can get the file extension of the workbook file with the following formula: 

=MID(CELL("Filename",A1),FIND(".",CELL("Filename",A1),FIND("[",CELL("Filename",A1)))+1,FIND("]",
CELL("Filename",A1),1)-FIND(".",CELL("Filename",A1),FIND("[",CELL("Filename",A1)))-1)

If you prefer a VBA function, use the following: 

Function Extension(R As Range) As String
    Extension = Mid(R.Worksheet.Parent.FullName, _
        InStrRev(R.Worksheet.Parent.FullName, ".") + 1)
End Function
ShortFadeBar
LastUpdate This page last updated: 6-June-2011.

-->