The FILENAME formula with example in Excel

Excel does not have a built-in FILENAME function, but you can use a combination of functions to get the name of the current workbook or the path to the current workbook. The CELL function is commonly used for this purpose.

Using the CELL Function to Get the File Name

The CELL function can return various information about the cell, including the file name and path.

Syntax

excel
CELL(info_type, [reference])

• info_type: A text value specifying the type of information you want. To get the file name and path, you use "filename".
• reference: Optional. The cell reference you want information about. If omitted, the current cell is used.

Example: Getting the Full Path and File Name

1. In any cell, enter the formula:

   excel
   =CELL("filename")
   

   This will return the full path, file name, and sheet name of the workbook in the format:

   vbnet
   C:\path\to\your\file\[WorkbookName.xlsx]SheetName
   

Extracting Just the File Name

To extract just the file name from the full path, you can use additional text functions such as MID, FIND, and LEN.

1. In cell A1, enter the formula to get the full path:

   excel
   =CELL("filename", A1)
   

2. In cell B1, enter the formula to extract the file name:

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

Explanation

• FIND("[", A1) + 1: Finds the position of the opening square bracket [ and adds 1 to get the start position of the file name.
• FIND("]", A1) - FIND("[", A1) - 1: Calculates the length of the file name by finding the position of the closing square bracket ], subtracting the position of the opening square bracket [, and adjusting by -1.

Example: Getting Only the File Name without Extension

To further remove the file extension, you can use the LEFT and FIND functions.

1. In cell C1, enter the formula to remove the extension:

   excel
   =LEFT(B1, FIND(".", B1) - 1)
   

Explanation

• LEFT(B1, FIND(".", B1) - 1): Extracts the left portion of the file name up to, but not including, the first period . which denotes the start of the file extension.

Practical Use Cases

1. Documentation: Automatically inserting the file name in headers, footers, or cells for documentation purposes.
2. File Management: Using the file name in formulas to manage multiple workbooks or for referencing purposes.
3. Dynamic Referencing: Creating dynamic reports or dashboards that adapt to the file name.

Summary

While Excel does not have a dedicated FILENAME function, you can use the CELL function combined with text functions to get the file name, path, and other related information. This approach allows you to extract and manipulate file-related details as needed in your Excel worksheets.