Get File Names from a Folder into Excel (Copy Files Names to Excel)

As we work with Excel, we may need to extract the file names of files in local or network folders for, for example, documentation.

We can do this the old-fashioned way of copying and pasting, but it is tedious, inconvenient, and time-consuming, especially when processing many files.

This tutorial shows three time-saving techniques for getting file names from a folder or subfolders in Excel.

How to Get Names of Files Within the Main Folder

The methods in this section are limited to getting the filenames of files in the main folder.

The following section will present using the Get & Transform feature to extract the names of files in the main folder and subfolders. 

Using the FILES Function

We can use the FILES function to return a list of names of files in a folder.

One important thing to know about the FILES function is that it only works in named cell ranges but not Excel cells.

Consider the “Excel Tutorials” folder on my local C drive.

Notice that the folder has 23 items, 22 files with various extensions, and one subfolder called “Excel Tutorials 2.”

We want to use the FILES function to extract the names of the 22 files in the main folder in an Excel file.

We use the following steps:

1. Select cell A1 and enter the full path of the “Excel Tutorials” main folder followed by an asterisk (*) symbol.

Note: If you do not know the full path of the main folder, you can get it using the below steps:

• Open any of the existing workbooks in the main folder (for which you want to get the folder path) or create and save a new Excel file in the same folder and then open it.
• Select any cell in the workbook and enter the formula below:

=REPLACE(CELL("filename"),FIND("[",CELL("filename")),LEN(CELL("filename")),"*")

Notice that this formula returns the full path of the main folder followed by an asterisk (*) symbol.

• Press Ctrl + C to copy the cell contents, and press Ctrl + Alt + V to paste it as values in cell A1 or any other cell of the worksheet in which you want to get the file names. 
• If you create a new file to get the main folder’s path, delete it so that it is not included in the names of the files to be extracted.

2. On the Formulas tab, on the Defined Names group, click the Define Name button.

3. Do the following on the New Name dialog box that appears:

• On the Name box, enter List_File_Names or any other name you choose. Remember not to put any spaces in the name (as named ranges are not allowed to have spaces in between).
• Open the Scope drop-down and select Workbook.
• Enter the formula =FILES(Sheet1!$A$1) on the ‘Refers to’ field. If you chose a different cell for the folder’s path, update the formula accordingly (so change $A$1 with the cell reference that you are using). 
• Click OK.

4. Select cell A4, where we want the list to start, and enter the following formula:

=IFERROR(INDEX(List_File_Names,ROW()-3),"")

Note: Update the formula if you start the list in a different cell. For example, if you start the list in cell A5, subtract the value four from the ROW function inside the INDEX function.

5. Drag the Fill Handle feature to copy the formula down the column and get the list of the filenames of the files in the main folder. 

Note: FILES is a Macro-4 function, and you need to save your workbook as a Macro-Enabled Workbook (*.xlsm) to avoid losing the list when you save the file. Excel informs you accordingly when you attempt to save the workbook as a regular .xlsx file:

Alternatively, you can copy the list and paste it as values to avoid the need to save the file as a macro-enabled workbook.

How to Get Files Names With a Particular Extension

If you want to extract the filenames of the files with a specific extension, replace the asterisk (*) symbol with that particular file extension. 

For example, if you want to get only the files names of macro-enabled workbooks, you must replace the asterisk with *xlsm extension as in the example below:

When you use an asterisk symbol (*) at the end of the folder path address, it tells the FILES function to fetch all the files irrespective of the file extension. But when you use *xlsm, it forces the FILES function to only give us the list of those file names that end with xlsm

Explanation of the FILES function technique

The FILES formula, =FILES(Sheet1!$A$1), extracts the filenames of the files in the main folder. 

In the =IFERROR(INDEX(List_File_Names,ROW()-3),””) formula, the names returned by the FILES function are fed to the IINDEX function as an array, and then the ROW function returns the first file name, second file name, third file name, and so on. 

Notice that we used ROW()-3 because we started the list in the fourth row. So ROW()-3, equivalent to 4-3 when the row number is 4, returns the first file name. Next, ROW()-3 equals 2 (i.e., 5-3) when the row number is 5, returns the second file name, and so on.

Lastly, the IFERROR function wrapping the formula suppresses the #REF! Error and returns an empty string after the formula returns the last file name.

Notice that this technique did not return the files names of the files in the “Excel Tutorials 2” subfolder. 

Also read: Extract Last Name in Excel

Using a User-Defined Function (Created using VBA)

We can create a User Defined Function using Excel VBA to return the names of files in a folder.

The advantage of this method over Method #1 is that the function can be saved in a personal macro workbook and reused without repeating the steps.

We use the below steps to create the User Defined Function:

1. Press Alt + F11 to launch the Visual Basic Editor.
2. In the Visual Basic Editor, select any object of the workbook you are working on, click Insert on the menu bar, and choose Module.

A new module is added to the project in the Project window. 

Note: If you do not see the Project window, activate it by pressing Ctrl + R or choosing Project Explorer on the View menu.

3. Double-click the new module to open its code window on the right.
4. Copy and paste the following function procedure into the module’s code window.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function EXTRACTFILENAMES(ByVal FolderPath As String) As Variant
Dim i As Integer
Dim files As Variant
Dim fileObj As Object
Dim folderObj As Object
Dim fileColl As Object
Dim fsoObj As Object
Set fsoObj = CreateObject("Scripting.FileSystemObject")
Set folderObj = fsoObj.GetFolder(FolderPath)
Set fileColl = folderObj.files
ReDim files(1 To fileColl.Count)
i = 1
For Each fileObj In fileColl
files(i) = fileObj.Name
i = i + 1
Next fileObj
EXTRACTFILENAMES = files
End Function

5. Save the procedure and the workbook as a Macro-Enabled Workbook (*.xlsm).

How to Use the User-Defined Function in the Workbook

Use the following steps to put the function into action:

• Select any cell and enter the full path of the folder from which we want to extract the file names. In this case, we enter the full path in cell A1:

• In the cell from which you want to start the list of file names (in this case, it is A4), enter the following formula (update the formula according to the cell from which you want to start the list):

=IFERROR(INDEX(EXTRACTFILENAMES($A$1),ROW()-3),"")

• Drag the Fill Handle feature to copy the formula down the column, and you will get the list of the filenames of the files in the “Excel Tutorials” folder.

Note: In case you do not want to enter the path of the folder in a cell, you can hard-code the path into the formula as shown below:

=IFERROR(INDEX(EXTRACTFILENAMES("C:\Excel Tutorials"),ROW()-3),"")

If you want to start the list in row 1, you can modify the formula as below:

=IFERROR(INDEX(EXTRACTFILENAMES("C:\Excel Tutorials"),ROW()),"")

Explanation of the Formula

=IFERROR(INDEX(EXTRACTFILENAMES($A$1),ROW()-3),””)

In INDEX(EXTRACTFILENAMES($A$1),ROW()-3) part of the formula, the EXTRACTFILENAMES function returns an array of all the file names of the files in the main folder.

The array of file names is then fed into the INDEX function, and the ROW function returns the first file name, second file name, third file name, and so on.

We used ROW()-3 in the formula because we started the list in the fourth row. So ROW()-3, equivalent to 4-3 when the row number is 4,  returns the first file name.

Next, ROW()-3 equals 5-3 when the row number is 5, returns the second file name, and so on.

Finally, the IFERROR function enclosing the formula suppresses the #REF! errors and returns empty strings after the formula returns the last file name.

Notice that this technique did not return the names of the files in the “Excel Tutorials 2” subfolder. 

Using User-Defined Function to Get List of File Names With a Specific Extension 

Sometimes we may want a list of names of only those files with a specific extension, for example, .docx.

We can create a User-Defined Function that we can use to return only those file names with a particular extension.

We use the below steps to create the User Defined Function:

1. Press Alt + F11 to launch the Visual Basic Editor.
2. In the Visual Basic Editor, select any object of the workbook you are working on, click Insert on the menu bar, and choose Module.

A new module is added to the project in the Project window. 

Note: If the Project window is not open, activate it by pressing Ctrl + R or choosing Project Explorer on the View menu.

3. Double-click the new module to open its code window on the right.
4. Copy and paste the following function procedure into the module’s code window.

'Code developed by Steve Scott from https://spreadsheetplanet.com
Function EXTRACTFILENAMESBYEXT(ByVal FolderPath As String, FileExtension As String) As Variant
Dim i As Integer
Dim files As Variant
Dim fileObj As Object
Dim fsoObj As Object
Dim fileColl As Object
Dim folderObj As Object
Set fsoObj = CreateObject("Scripting.FileSystemObject")
Set folderObj = fsoObj.GetFolder(FolderPath)
Set fileColl = folderObj.files
ReDim files(1 To fileColl.Count)
i = 1
For Each fileObj In fileColl
If InStr(1, fileObj.Name, FileExtension) <> 0 Then
files(i) = fileObj.Name
i = i + 1
End If
Next fileObj
ReDim Preserve files(1 To i - 1)
EXTRACTFILENAMESBYEXT = files
End Function

5. Save the function procedure and the workbook as a Macro-Enabled Workbook (*.xlsm).

How to Use the Above User-Defined Function to Get File Names from a Folder

• Select any cell in a worksheet and enter the full path of the folder containing the files whose names you want to extract. In this case, I have entered the folder’s full path in cell A1. 

• Enter the extension of the file names you want to extract in another cell. You can also enter a relevant search term. In this case, I have entered the png file extension in cell B1 because I want to extract the file names of images.

• Enter the following formula in a cell where you want the list to start. In this case, I have entered the formula in cell A4:

=IFERROR(INDEX(EXTRACTFILENAMESBYEXT($A$1,$B$1),ROW()-3),"")

• Drag the Fill Handle to copy the formula down the column and get all the names of the image files in the main folder.

In this example, since I entered the function in cell A4 (which is row number 4), I used ROW()-3, as I wanted to start the numbering from 1 (where the ROW function would give 4 as the function is in cell A4). If you use it in any other cell, you need to adjust this part accordingly.

In case you want to fetch all the files in the folder, leave the extension argument blank (which is cell B1 in our example).

Also read: Remove Middle Name from Full Name in Excel

How to Get Names of Files Within the Main Folder and the Subfolders

The methods we covered in the previous section of this tutorial can only extract the names of files in the main folder.

But what if you want to extract all the names in the main folder as well as all the file names in all the subfolders as well.

This can easily be done using the Get & Transform feature (known as the Power Query feature).

Using the Get & Transform Feature (Power Query) 

Get & Transform is a feature in Excel that enables us to extract, transform, and load data from various sources.

For example, we can use this feature to extract the names of all the files in a folder and its subfolders.

Let’s look at the following “Excel Tutorials” folder on my local C drive:

The folder has 22 files and one subfolder called “Excel Tutorials 2.” Let me open the subfolder we see what it contains:

The subfolder contains six files with various extensions.

We want to use the Get & Transform feature to extract the names of the 22 files in the main folder plus the names of the six files in the subfolder. 

We use the following steps:

1. On the Data tab, on the Get & Transform Data group, open the Get Data drop-down, select From File, and choose From Folder on the sub-menu.

2. Use the Browse feature to locate the “Excel Tutorials” folder, select the folder and click Open.

On the dialog box that appears, we can see the list of names of files in the main folder and subfolder alongside other metadata.

3. Click the Transform Data button at the bottom of the dialog box.

4. In the Power Query Editor, right-click any column that you don’t want to appear in the final list of file names and choose the Remove option on the shortcut menu. In the following example, I want to remove the Content column:

In the example below, I have removed all columns except the Name column:

5. Click the Close & Load button.

The names of the 22 files in the main folder, plus the names of the six files in the subfolder, have been loaded onto a worksheet:

This tutorial showed three Excel techniques for extracting the names of files in folders. We hope you found the tutorial helpful.

Other Excel articles you may also like:

• How to Open an MPP file in Excel?
• How to Open XML Files in Excel?
• How to Separate Excel Tabs Into Separate Files
• How to Open DAT Files in Excel?
• How to Get Sheet Names in Excel?
• VBA to Check If Folder Exists
• How to Select Multiple Items from a Drop Down in Excel?
• How to Insert an Excel file into MS Word?