Excel Tips How To Easily Extract File Name In Folder

If you want to extract the file names of the files contained in a certain folder, put them in the worksheet for other uses, it is very troublesome to input them one by one or copy and paste them. In fact, we can use the two excel functions FILE and INDEX together to extract the file name in a special folder and then paste the file name to an excel worksheet easily.

1. Extract Excel File Name In Folder Steps.

1. First, open the Excel file, click the “Formulas” tab, click the “Define Name → Define Name” menu item in the Defined Names area.
2. Input a name ( for example Get_File_Names ) according to your needs in the Name: input text box of the pop-up New Name window.
3. Enter “=FILES(“D:\users\tom\documents\*.*”)” in the Refers to: input text box.
4. Note: The quotation marks in the function are English quotation marks, and the content in the quotation marks is the absolute path of the folder where the file name is to be obtained.
5. The syntax format of the FILES function: FILES(directory_text). Where directory_text specifies which directory to get the file name from, it must be an absolute path.
6. The directory_text accepts the wildcard question mark(?) and asterisk (*). The ? sign matches any single character, and the * sign matches any sequence of characters.
7. For example, if directory_text is “D:\users\tom\documents\*.doc“, it means that all the documents with the extension “.doc” are returned.
8. The value of directory_text can be obtained by the following method, open the folder where the file name is to be obtained, click in the address bar of the folder so that the absolute path of the folder is selected, press CTRL + C to copy it, and press CTRL + V to paste it.
9. Next, enter “=INDEX ( Get_File_Names , ROW(A1))” in cell A2 of the worksheet, and then pull down until an error value appears which means finish.
10. If the input in the name Refers to: is “=FILES(“D:\users\tom\documents\user*.*”)“, you can only extract all files that contain the word “user” at the beginning of the file name.
11. Of course, you can also apply MID, LEN, and other functions on the extracted file name to extract the pure file name without the extension.
12. For example, enter “=MID(A2,1, LEN(A2)-4)” in cell B2 and drag it down to fill, so that the required pure file name is extracted.

2. Extract Excel File Names In Folder Using VBA.

1. You can use VBA script code to extract excel file names in a folder also.
2. You should loop through all the files in the specified folder in VBA.
3. And then check for each file to verify whether it is an excel file or not.
4. If the file is an excel file, then extract the file name.
5. Below is an example source code, you can see the comments for the detailed explanation.

   ' This function will loop through a folder and extract the excel file's name in the folder only.
   ' The input parameter is the parent folder path.
   Sub ExtractFiles(ByVal parentFolder As String)
   
      ' Define the return string value.
      Dim ret As String
       
      ' Define 3 variables,
      Dim obj As Object, pFolder As Object, file As Variant
      
      ' Get the parent folder object.
      Set pFolder = obj.GetFolder(parentFolder)
      
      ' Loop through the parent folder and get each file in the folder.
      For Each file In pFolder.Files
         
         ' If the file name contains ".xls" then it is an excel file
         If InStr(file.name, ".xls") > 0 Then
            
            ' concat the excel file name to the returned string.
            ret = ret & file.name & ','         
   
         End If
      Next file
   
      ' Return the files name string.
      return ret
   End Sub