Excel Tips n Tricks : Tip 7 (Generating List of Filenames in Excel 2013)

Hello everybody! I hope you're doing fine. Here I have another trick that you will love to learn and share. This article will help you to learn how to get the file names inside any folder/directory. If you've started thinking after reading the preceding lines that I'm going to use any VBA kind of thing then let me tell you that is incorrect. The trick is much simpler than that because I'll be using only a Formula and Defined Names.

The technique uses an Excel 4 XLM style macro function in a "Named Formula" (a defined name that points to a formula). We're going to use the "FILES" function. It's not a normal function; it's a macro function that takes one argument as a directory path and file specification. I'll be explaining the file specification part of the argument later in this article. Since this function is intended to execute inside a macro or defined name, you cannot access this function in your normal workbook in Excel 2013, you need to save your file as ".xlsm" or ".xls", in other words for Excel 2013 (Macro Workbook) or Excel 97-2003 format. Use the following procedure.

Step 1: Go to "Formulas" > "Define Name".

Step 2: Type "ListOfFiles" as Name in the New Name Dialog Box. (You're free to give any name of your choice.)

Step 3: Enter the following formula in the "Refers To" Field:

"=FILES(Sheet1!$A$1)" (the Range inside the function refers to the cell where we will enter the folder/directory path of which we need the file names in)

Step 4: Click "OK" to save the defined formula.

Now let me return and explain "file specification". See the examples below:

"E:\Songs\mp3\*.*", "E:\Files\*.xls", "E:\Docs\*.xl*"

Here "E:\Songs\mp3\", "E:\Files\" & "E:\Docs\" are the directory path and "*.*", "*.xls" & "*.xl*" are the file specifications (the remaining part after the directory path).

Wildcards (?*) are used in conjunction with text to specify what to filter out. If you're a DOS guy then you must be aware of what they are. Anyway, let me explain wild cards a little. They are characters that help you specify what to select out of all. A question mark "?" represents a single character in a string and an asterisk "*" represents all characters. A file name usually consists of two parts, a primary name (before the dot) and an extension name (after the dot). You must have seen your file name like "myFile.xlsx". Here "myFile" is the primary name and "xlsx" is the extension name. The extension name basically tells the computer what kind of file it is and what applications are supposed to open this file.

So, if you write: "D:\MySongs\*.mp3" then this will return all the filenames having mp3 inside folder "D:\MySongs\", you will see the first part is "*", in other words all/any and second part is "mp3". I hope you understand what a file specification is so far. Let's take another example; "E:\MySongs\*.mp*", here the second part contains "*.mp*", so it will select all the files having the first two characters in their extension name as "mp" and any character after that, in other words mp3, mp4, mp5 and so on. I think that's enough to understand what a file specification is. Let's return to the topic now.

So, after defining the formula (the steps continue...):

Step 5: Enter a directory path and file specification in Cell A1 in Sheet1, say:

"D:\Songs\one direction\*.mp3" (the folder path that you want file names in)

Then we use the Index function to get the file name from the array of filenames one by one.

Step 6: Enter and copy down this formula to the column:

"=INDEX(ListOfFiles,ROW()-1)"

The ROW() function returns the row number of the current cell. Since the same formula is copied down to the column, hence, as used here (ROW()-1), it will generate a series of consecutive integers: 1, 2, 3, 4, 5 and so on. Note that after a certain number of cells down the column it will show you the reference error "#REF!". They denote that the formula is accessing the index number of a filenames array that doesn't exist.

See the magic in the pictures below:


Picture 1 (How to create a defined formula -1)