How to copy file names into Excel
By
SpreadCheaters
You can watch a video tutorial here.
When working with automation in Excel through macros, you may need to get the names of all the files in a folder to perform some operation on them such as merging. Instead of manually typing out each file name or copying and pasting it from Windows Explorer, there is an easy way to copy all the file names in a folder.
Step 1 – Open the folder with the files
– Go to File > Open > Browse
– Find the folder with the files whose names you need
Step 2 – Copy the path of the files
– Select the files
– Press Shift and then right-click to open the context menu
– Select Copy as Path
– Select Cancel to close the dialog box and click on the left arrow to close the File menu
Step 3 – Paste the file paths and open the Convert Text to Columns wizard
– Select the first cell in the worksheet and press Ctrl+V
OR
Right-click and select Paste from the context menu
– Go to Data > Data Tools > Text to Columns
– Select Delimited
– Click Next
Step 4 – Select the delimiter
– Under Delimiters select Others and type in a backslash (\)
– Check the Data preview so see if the display is correct
– Click Finish
Step 5 – Delete the unnecessary columns
– Select the columns that do not contain the file name
– Right-click and select Delete from the context menu
Step 6 – Check the list of file names
– The file names will be displayed with their file extensions
