In this advanced excel tutorial, you’ll learn to extract multiple filenames from a folder to an excel worksheet. You’ll be able to extract filenames, file paths, and file extensions.

As a video creator, I’ve to create tons of videos. I have to make the same number of scripts, if not more, thumbnails, and a lot of supporting files. Now I need to list all the files and run some analysis. I’d like to know how many videos I’ve made. Does each video has a thumbnail or other related files? What are the file formats of those videos? You know, something to fudgel. But, the catch here, how I would do it? 

I need to extract all the file names from a folder to a column. I’ll extract the file path of each file inside that folder. Also, I’ll need to extract the file type or the file extension from that same folder. And I need to do it fast. I mean really fast. Say within 20 seconds.

Again, there are thousands of answers to this question, yet I’ll only focus on Excel solutions. Yes, I’ll do it in MS Excel. Why? Don’t you get it? I only know how to do it in excel. It’s that simple. If I’d known anything other than excel, I’m sure this site name wouldn’t be MS Excel Tutorial. Enough fluff & fillers. Let’s get to work.  

We have three objectives here.

1. Copy all the file names into excel from a folder

2. Copy the file path (file location) of each file

3. Copy file extension (file type) of each file.

We have one shot and 20 seconds to do it. Don’t count the time while you are reading. That’s excluded. 

Like always, you don’t need to be tech-savvy to solve this. Just the basics would do, like how to open excel, copy paste a few lines, click on some buttons, and stuff. But this is an advanced excel tutorial because I’ll be using Visual basic for application for this task. I’ll create a macro when I run it; it will list the filenames, paths, and extensions on my excel workbook.

Let’s follow the steps and copy some filenames.

1. Open Microsoft Excel. (Start counting).

2. On the “Ribbon,” you’ll find the “Developer Tab” click it.

3. Click on “Visual Basic.”

4. Create a Module by clicking on “Insert” and then “Module.”

5. Paste the code in the VBE (Visual Basic Editor).

6. See that Green Triangle? That’s the run button. Click it.

7. Select the folder where you have the file. 

8. Click on “OK,” and you’ve done it. 

How long did it take? Let me know in the comment. 

Now let’s take a moment and get a basic idea of how the code worked. 

We created a macro named “Get File List.” Then we declare some crucial variables. Now we prepare the code to open the file explorer to select the folder from where excel will extract the filenames, paths, and file type. The next few lines of the code will extract filenames to designated columns. The macro will import the file path and file names with extensions. We also separated the file name and the extension and then placed them in two different columns using the Left and the MID function inside the VBA. Done. If you need to join the filename and path and put it in a single cell, you can use the CONCAT function.  If you still think this is tough for you, please check this tutorial, How to Import Each File Details from a Folder into Cells in Excel. This tutorial will help you import File Name, File Path, File Extension, and some essential data like Date Created, Date Accessed & Date Modified. On top of it, you don’t need to use any VBA here. I have used excel built-in feature, and that’s super easy. See you there. Have questions? Shoot in the comment section below. 

PS. Don’t forget to check How to Rename Multiple Files at Once.