Sometime you need to insert the file name list of any format from any specific folder to active worksheet in Excel. This could be done by copy the file name one by one and paste them in the active worksheet. You can use given latest Excel trick to get the file names from a folder using Excel function.
All we know folder is a place which you have to store different format files, sub folder as per your need. In that situation most of the person have to use copy and paste method. They have select and copy the file name one by one and paste it at any blank cell to prepare the list. It’s very boring and consume lot of time to prepare the file name list.
Must Read: 2 Methods to insert more than one columns and rows in Excel
Latest Excel trick to Get the File Names from a Folder using Excel function
In this article we have to discuss an easy way which helps you to quickly get the file names from a folder using Excel function.
Step 1: Copy the folder location and paste it in Cell C1 in which you have different format files. For eg: C:\Users\admin\Desktop\lucky\Important Excel Sheet
Step 2: Double click the folder location and put the * wildcard at the end of the path like this C:\Users\admin\Desktop\lucky\Important Excel Sheet\* . All we know * wildcard is used to search the file names.
Step 3: Type the given following formula in Cell B4.
=SUBSTITUTE(CELL(“FILENAME”),RIGHT(CELL(“FILENAME”),LEN(CELL(“FILENAME”))-FIND(“@”,SUBSTITUTE(CELL(“FILENAME”),”\”,”@”,LEN(CELL(“FILENAME”))-LEN(SUBSTITUTE(CELL(“FILENAME”),”\”,””))),1)),”*”)
You view an error message after pressing the enter key.
Step 4: Now, save this file in the same folder which list you want to get. Now, again double click on cell B4 to edit the formula in which you already applied the function. You can check the same path location is shown in that cell which file name list you want to bring in active worksheet.
Step 5: Select the Cell C4 and create the range name with from Formulas > Define Name option. Type the name and type the given function in the Refers to: box =INDEX(FILELIST,1) and press OK button.
Now, You must have to update the existing function of cell C4 with =INDEX(FILELIST,ROWS($C$17:C17)) and press enter.
Step 6: Now, drag this function up to the required cells. You can view all the file name list in the active worksheet.
Must Read: How to use Index Match function in Excel
I hope after reading given latest trick you can easily understand how to get the file names from a folder using Excel function. If you have any suggestion then please write us your views in the comment box. Thanks to all.