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

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.


Download Practice Workbook

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


4 Easy Ways to Copy File Names from Folder to Excel     

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.


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 see, all the file names are available in the sheet.


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 will 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 is giving 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.


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.

Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo