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’.
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.
- 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.
- Later, go to your Excel worksheet, select a cell, and press Ctrl + V. We selected Cell B5.
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.
- 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.
- Then click on Define Name from the Formulas ribbon.
- 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–
- After that, insert the following formula in Cell B8–
- Hit the Enter button.
- 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.
- Firstly, click as follows: Data > Get Data > From File > From Folder.
- Browse the folder and press Open.
- After a while, you will get a window like the image below. Just click Transform Data.
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.
- Press Alt + F11 to open the VBA window.
- Then click Insert > Module to open a new module.
- 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.
- 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–
- 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.
- Follow the first two steps from the previous section to open a new module.
- Insert the following codes in a new module-
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.
- Copy the file path in a cell and insert the extension name in another cell.
- Then type the following formula in Cell B10 and press Enter–
- 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.
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.