How to Copy File Names from Folder to Excel (4 Easy Methods)

Get FREE Advanced Excel Exercises with Solutions!

Do you have a lot of files in your file explorer to copy the names in an Excel worksheet and searching ways to avoid manually copying them? Then we think, you have visited the right place. We’ll show the best 4 methods to copy file names from a folder to Excel.


How to Copy File Names from Folder to Excel: 4 Effective Ways

Here’s our sample dataset which contains 5 files in a folder named ‘ExcelDemy’.

How to Copy File Names from Folder to Excel


1. Using Shortcut Keys to Copy File Names from Folder

First, we’ll learn the easiest method to copy file names from a folder to Excel. Nothing, we’ll just use the common copying shortcut keys Ctrl + C.

Steps:

  • Select all the files by pressing Ctrl + A.
  • Then press and hold the Shift key and right-click your mouse.
  • Click Copy as path from the context menu.

Using Shortcut Keys to Copy File Names from Folder to Excel

  • Later, go to your Excel worksheet, select a cell, and press Ctrl + V. We selected Cell B5.

Paste the File Names from Folder to Excel

Now our target is to remove everything except the File name. For that, we’ll use the Find and Replace tool.

  • Select all the copied values.
  • Next, press Ctrl + H to open the Find and Replace tool.
  • Type part of the address before the file name on the Find what box.
  • Keep the Replace with box empty.
  • Finally, just press Replace All.

Now look, there are remaining only the file names.

Read More: How to Get Filename from Path in Excel


2. Copy File Names from Folder by Using FILES Function

We can copy file names from a folder to Excel by using a function too. The FILES function can be used for that, but it is not a worksheet function. It only works with named ranges.

Steps:

  • First, copy the file path in a cell with an asterisk at the end. We coped in Cell B5. This cell reference will be used in the named range.

Using FILES Function to Copy File Names from Folder to Excel

  • Then click on Define Name from the Formulas ribbon.

Define Names to Copy File Names from Folder to Excel

  • Give a name for the named range.
  • Select Workbook from the Scope drop-down box.
  • Next, type the following formula in the Refers to box and press OK
=FILES(Sheet1!$B$5)

Set Name and Refers to Copy File Names from Folder to Excel

  • After that, insert the following formula in Cell B8
=IFERROR(INDEX(FileNames,ROW()-7),"")
  • Hit the Enter button.

Formula Breakdown:

  • The FILES formula will return the file names from the specified folder.
  • Then we used the INDEX function that will return the names serially from the array by using the output of the FILES and ROW Note that we used ROW()-7, as we started from the 8th row onwards. So ROW()-7 will be 1 for the first name, 2 for the second name, and so on.
  • Finally, the IFERROR function will return the original output if it doesn’t get any error and will return empty if it gets an error.

  • Lastly, use the Fill Handle tool to copy the formula.

Now, all the file names are available in the sheet.

Read More: How to Rename Files in a Folder in Excel


3. Using Power Query to Copy File Names from Folder

If you want to avoid formulas then there is another exclusive way to copy file names from a folder to Excel. The Excel Power Query is the tool that can do it.

Steps:

  • Firstly, click as follows: Data > Get Data > From File > From Folder.

Using Power Query to Copy File Names from Folder to Excel

  • Browse the folder and press Open.

Select Folder to Copy File Names from Folder to Excel

  • After a while, you will get a window like the image below. Just click Transform Data.

Press Transform Data to Copy File Names from Folder to Excel

Then we’ll get the Power Query window. It will have different columns with data. We’ll keep only the name column.

  • Select all the column headings except the name column.
  • Then right-click your mouse and select Remove Columns from the context menu.

  • Finally, just press Close & Load.

A few moments later, a new sheet will open up with the folder name as a sheet name.


4. Run a VBA Code to Copy File Names from Specific Folder

Using VBA is the quickest way to copy file names. We’ll make a User Defined Function (UDF) and then use it for the path address.


4.1 Copy All File Names

Our first  User Defined Function (UDF) function will copy all the file names of every extension. The function name will be Get Names.

Steps:

  • Press Alt + F11 to open the VBA window.
  • Then click Insert > Module to open a new module.

Using VBA to Copy File Names from Folder to Excel

  • Then type the following codes-
Function GetNames(ByVal FolderPath As String) As Variant
Dim mitResult As Variant
Dim i As Integer
Dim mitFile As Object
Dim mitFSO As Object
Dim mitFolder As Object
Dim mitFiles As Object
Set mitFSO = CreateObject("Scripting.FileSystemObject")
Set mitFolder = mitFSO.GetFolder(FolderPath)
Set mitFiles = mitFolder.Files
ReDim mitResult(1 To mitFiles.Count)
i = 1
For Each mitFile In mitFiles
mitResult(i) = mitFile.Name
i = i + 1
Next mitFile
GetNames = mitResult
End Function
  • Next, go back to your sheet.

Type VBA Codes to Copy File Names from Folder to Excel

  • Later, copy the file path in a cell with an. We coped in Cell B5.

  • Now our function is ready, it has only one argument. write the following formula in Cell B8 and press Enter
=IFERROR(INDEX(GetNames($B$5),ROW()-7),"")

  • Finally, use the Fill Handle tool for copying the formula.

Here are all the file names.


4.2 Copy All File Names with a Specific Extension

Now we’ll make a function- GetNamesbyExt that will have two arguments- file path and extension. So it will return the file names according to the extension name.

Steps:

Function GetNamesbyExt(ByVal FolderPath As String, FileExt As String) As Variant
Dim mitResult As Variant
Dim i As Integer
Dim mitFile As Object
Dim mitFSO As Object
Dim mitFolder As Object
Dim mitFiles As Object
Set mitFSO = CreateObject("Scripting.FileSystemObject")
Set mitFolder = mitFSO.GetFolder(FolderPath)
Set mitFiles = mitFolder.Files
ReDim mitResult(1 To mitFiles.Count)
i = 1
For Each mitFile In mitFiles
If InStr(1, mitFile.Name, FileExt) <> 0 Then
mitResult(i) = mitFile.Name
i = i + 1
End If
Next mitFile
ReDim Preserve mitResult(1 To i - 1)
GetNamesbyExt = mitResult
End Function
  • Next, go back to your sheet.

Type VBA Codes to Copy File Names from Folder to Excel

  • Copy the file path in a cell and insert the extension name in another cell.

Insert File Path and Extension to Copy File Names from Folder to Excel

  • Then type the following formula in Cell B10 and press Enter
=IFERROR(INDEX(GetNamesbyExt($B$5,$B$7),ROW()-9),"")

Insert Formula with User Defined Function to Copy File Names from Folder to Excel

  • Use Fill Handle to finish.

It gives the file names with xlsx extension.

Our folder has doc files too, insert docx in Cell B7 and it will return the doc file names.


Download Practice Workbook

You can download the free Excel workbook from here and practice independently.


Conclusion

That’s all for the article. I hope the above procedures will be good enough to copy file names from a folder to Excel. Feel free to ask any question in the comment section and please give me feedback.


<< Go Back to Excel File Name | Excel Files | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

2 Comments
  1. Thankq thanku very much for your clear input, you saved my time of 3 hours into 5mins by using step 3

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo