In this article, we explore the different ways of getting the Excel file name directly from Excel. Often, mastering a variety of features and functions is necessary to fully utilize your Excel files. Extraction of the Excel file name, an apparently simple but incredibly useful task, is one such powerful ability.
By mastering these methods, you’ll gain access to an essential skill that offers accuracy and efficiency, much like the transformative dynamic array formulas. So come along as we explore the advantages of the file name extraction techniques available in Excel and show you how to add them to your spreadsheet tasks.
Download Practice Workbook
You can download the practice workbook from the following download button.
How to get the Current Excel File Name from the Excel File Path: 4 Methods
Method 1: Using the CELL Function to get the Excel File Path
- In Excel, you can use the CELL Function to get the current location of your opened Excel file. By entering the following formula in the Excel sheet you will get the cell directory in cell C5.
=CELL("filename")Here,
The entire directory path—along with the names of the active worksheet and the Excel file—is returned by this formula. The output will be shown in cell C5, which you can edit to your specifications. In the directory of the Excel file, you will find three parts named: Path, Excel File name, and Worksheet Name.
Method 2: Using the CELL, MID, and SEARCH Functions to get the Excel File Name
In order to separate the Excel file name from the full path, we will use a combination of Excel functions in this method. In order to find the file name within the path, this method makes use of the CELL, MID, and SEARCH functions.
- Enter the following formula in cell C6:
=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)Here,
This formula extracts the name of the current worksheet from the full file path in Excel. It searches for “[” and “]” to determine the worksheet name and uses the MID function to extract it.
Method 3: Combining the CELL, SUBSTITUTE, and LEFT Functions to get the Excel Path & File Name
- This method will extract the name and path of the Excel file using the CELL, SUBSTITUTE, and LEFT Now enter the following formula in cell C7:
=SUBSTITUTE(LEFT(CELL("filename"),SEARCH("]",CELL("filename"))-1),"[","")Here,
By using the SUBSTITUTE and LEFT functions to remove the brackets and any prior text from the full file path, this formula condenses the name of the current worksheet. It finds the “]” character, extracts the leftmost text, and gets rid of the “[” character.
Method 4: Using the CELL, SUBSTITUTE, LEN, RIGHT, and SEARCH Functions to get the Excel File Path Name
- In order to extract the actual path using this technique, a complex formula must be used to break down the full Excel path. Enter the following formula in cell C8:
=SUBSTITUTE(CELL("filename"), RIGHT(CELL("filename"),LEN(CELL("filename"))-SEARCH("[",CELL("filename"))+1),"")Here,
This formula is used to remove the sheet name and the “[” character, which marks the beginning of the sheet name, from the workbook filename. When you want to display only the path without the sheet information, it can be useful.
How to get the Excel File Name from the Multiple Excel File Paths: 6 Methods
1. Using the Find and Replace Feature to get the Excel File Name
- Firstly, select cells B5:B14 from the worksheet with Excel file paths >> go to the Home tab >> Editing group >> Find & Select >> Replace. Or you can simply press Ctrl+H.
- A new window titled “Find and Replace” will appear. From the window, type “*\” in the “Find what” box and click “Replace All”. Lastly, click OK.
- Finally, you will get the filename in range B5:B14 from the path within the same column.
2. Using the RIGHT, SUBSTITUTE, REPT, and TRIM Functions
- First, select cell C5 and enter the following formula in it.
=TRIM(RIGHT(SUBSTITUTE(B5,"\",REPT(" ",100)),99))
3. Using the LEN, SUBSTITUTE, FIND, and MID Functions
- You can combine the LEN, SUBSTITUTE, FIND, and MID functions in Excel to obtain the filename from a path. Simply enter the following formula, and the result will be in your control.
=MID(B5,FIND("*",SUBSTITUTE(B5,"\","*",LEN(B5)-
LEN(SUBSTITUTE(B5,"\",""))))+1,LEN(B5))
Formula Breakdown:
LEN(B5): Here, the LEN function returns the total length, which is 45.
LEN(SUBSTITUTE(B5,”\”,””): In this part, the SUBSTITUTE function substitutes this “\” character with blanks, resulting in “C:UsersPC 45Desktop[Excel File Name.xlsx]”. Hence, the LEN function returns the total length which is 41.
SUBSTITUTE(B5,”\”,”*”,45-41): In this argument, the SUBSTITUTE function returns the character substituting with “*”.
FIND(“*”,C:UsersPC 45Desktop*[Excel File Name.xlsx]): In this case, the FIND function will find the position of the character(*) displaying an output of 25, meaning it’s in the 25th position.
=MID(B5,23+1, LEN(B5): In this final part, the MID function deducts 24 characters from the string, starting from the 25th position. Thus
The result stands for “[Excel File Name.xlsx]”.
4. Using the IFERROR, SUBSTITUTE, FIND, LEN, CHAR, and MID Functions
- In some cases, format names can be found within a path. But occasionally, you only need to extract the file name, not the formats. In this situation, you can quickly obtain the filename in Excel by combining the SUBSTITUTE, LEN, CHAR, FIND, MID, and IFERROR To apply the formula, select cell C5 and enter the following formula:
=IFERROR(MID(B5,FIND(CHAR(1),SUBSTITUTE(B5,"\",CHAR(1),LEN(B5)-LEN(SUBSTITUTE(B5,"\",""))))+1,FIND(CHAR(1),SUBSTITUTE(B5,".",CHAR(1),LEN(B5)-LEN(SUBSTITUTE(B5,".",""))))-FIND(CHAR(1),SUBSTITUTE(B5,"\",CHAR(1),LEN(B5)-LEN(SUBSTITUTE(B5,"\",""))))+6),"")
5. Applying VBA User-Defined Function
- Now first open Visual Basic Editor by pressing Alt+F11, or click here to see the details about opening Visual Basic Editor.
- Enter the following User-Defined function in Module1 and save the module.
Function ExtractFileName(FullPath As String) As String
Dim List As Variant
List = VBA.Split(FullPath, "\")
ExtractFileName = List(UBound(List, 1))
End Function
- In cell C5, enter the following formula using the newly created function ExtractFileName:
=ExtractFileName(B5)
6. Using VBA Split Function
- Select cells B5:B14 >> Press Alt+F11 to open the Visual Basic Editor and enter the following VBA code in Module2 >> Press the Run
Sub filePath()
Dim filename As String
Dim x As Variant
For Each cell In ActiveSheet.Range("B5:B14")
x = Split(cell.Value, Application.PathSeparator)
filename = x(UBound(x))
cell.Value = filename
Next cell
End Sub
- Finally, you will get the Excel file name after running the VBA code.
Things to Remember
Avoid Special Characters: Avoid using special characters like slashes (/), backslashes (), colons (:), question marks (?), asterisks (*), and quotation marks (“”) when naming your Excel files. Different operating systems may have compatibility problems with these characters.
File Extensions: Depending on how they are formatted, Excel files have a variety of file extensions. The most popular file extensions for workbooks are “.xlsx” for standard workbooks and “.xlsm” for macro-containing workbooks. Use the proper extension to preserve compatibility and functionality.
Short and Clear: As long as you maintain the necessary information, keep file names short. When working with multiple files, long and complicated file names can be challenging to manage.
Conclusion
In summary, the Excel file name is a vital identifier for classifying and locating data. It is essential for improving user collaboration, version control, and data management. In order to promote effective data regaining and ensure a structured approach to data handling across various projects, a well-chosen file name should be descriptive, brief, and reflective of the content.
Frequently Asked Questions (FAQs)
Q1: Why is knowing the Excel file name and path important?
Answer: Understanding the file name and path helps Excel’s dynamic formulas, process automation, and effective data management.
Q2: Can these methods be applied to different Excel versions?
Answer: Yes, these methods are compatible with various Excel versions, including Excel 2010, 2013, 2016, 2019, and 365.
Excel File Name: Knowledge Hub
- Copy File Names from Folder to Excel
- Get Filename from Path in Excel
- Rename Files in a Folder in Excel
<< Go Back to Excel Files | Learn Excel
