2 Methods to Copy the List of All File Names in a Folder into Your Excel Worksheet

Sometimes you will need to list all the file names in a folder in an Excel worksheet. Thus, you can better manage those files. In this article, we will introduce two effective methods to copy the list in a folder.

In an Excel worksheet, you can make a menu of some important files. And you need to copy the list of all the file names into one worksheet. However, when there are hundreds or thousands of files, you will find it hard to do this. In the image below, you can see several files.

Now you need to copy the list of all the file names into a worksheet in another Excel file. If you copy and paste them one by one manually, you will certainly spend a lot of time and energy. At this time, you can use the two methods below.

Method 1: Use a New Text Document

Now follow the steps below and see how this method takes effect.

1. Before you perform this process, make sure that you have shown the file extensions in your computer. Click the button “Start” in your computer.
2. And then click the button “Control Panel”.
3. In the control panel, click the “Appearance”.
4. And then click the button “Folder Options”.
5. After that, choose the option “View” in the “Folder Options” window.
6. Next uncheck the option “Hide extension for known file types”.
7. And then click “OK” to save the setting.

Therefore, you can see all the extensions of files in your computer.

8. Come back to the folder, create a new text document and give it a new name. We will name it “DataNumen Sales Volume”.
9. And then open this text file.
10. After that, input the following characters into this file:

dir> 1.txt

11. Next save this file and close it.
12. In this step, change the extension of this file from “.txt” into “.bat”.
13. After that, you will see a new window pop up. Here you need to click the button “Yes” to confirm this change.
14. And then double click this file again. Next you will see a new text document with the name “1” in this folder.
15. Open this new text document in this folder. You will see all the name information in it.
16. After that, copy the necessary part in this file.
17. And then in the target worksheet, click the small arrow under the button “Paste”.
18. In the drop-down list, choose the option “Use Text Import Wizard”.
19. Next in the new window, choose the option “Fixed width”.
20. And then click the button “Next”.
21. In step 2 of the wizard, move the break line and make sure that the names of those files will be in one cell.
22. And then also click “Next”.
23. In step 3, click “Finish”. Thus, all the information will appear in the worksheet.

You can delete the other useless columns in the worksheet. By using this method, you don’t have to copy and paste the names one by one.

Method 2: Define Name

In this part, we will introduce another excellent method.

1. Click the tab “Formulas” in the ribbon.
2. And then click the button “Define Name”.
3. In the “New Name” window, input a name into the first text box. We will also name it “DataNumen_Sales_Volume”.
4. After that, input this formula into the “Refers to” text box:

=FILES(“C:\Users\Sample\Desktop\Sales Volume\*.*”) &T(NOW())

The path of the folder is “C:\Users\Sample\Desktop\Sales Volume” in this example. You need to change it into your actual folder path.

5. And then click the button “OK” in the window.
6. Now input the following formula into one cell in the worksheet:

=IF(ROW(A1)>COUNTA(DataNumen_Sales_Volume),””,INDEX(DataNumen_Sales_Volume,ROW(A1)))

In this formula, you need to change the define name into yours.

7. After that, click the fill handle of this cell and drag downwards. As you move your mouse, you will also find that all the names will appear in this column.
8. But you may also find that those results are the results of formulas. You still need to copy and paste them as texts.
9. Next delete the formulas in the worksheet.

Until now, you have gone through all the steps. This method is also very effective for this requirement.

A Comparison of the Two Methods

You can see that both of the two methods are very useful. Here we will compare the two methods in this part.

Comparison	Use a New Text Document	Define Name
Advantages	If you are not familiar with Name and the complex formula in cells, you can use this first method.	There are fewer steps in this method. You can finish your tasks quickly by using it.
Disadvantages	Compared with the second methods, there are more steps in this method. You still need to spend a lot of time.	The formulas in cells will produce the results that you need. Therefore, you still need to copy and paste them as values.

When you need to input all file names into a worksheet, you will know which method is the most suitable one.

Take Actions Quickly in a Data Disaster

It is unavoidable that you will meet with data disaster. In such an accident, your Excel files will most possibly be damaged. Therefore, you need to take actions quickly. In order to repair corrupt xls data quickly and safely, you can use out excellent Excel repair tool. This tool is able to fix most of the Excel problems.

Author Introduction:

Anna Ma is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Word doc data error and outlook repair software products. For more information visit www.datanumen.com