Assuming you want to extract the all the files from a folder “Folder_A” which is located in the C drive of your system.

  1. In cell A1, enter the folder complete address followed by an asterisk sign (*)
    • For example, if your folder in the C drive, then the address would look like

      C:UsersMyComputerFolder_A*

  2. If you are not sure how to get the folder address, use the following method:
    • In the folder from which you want to get the file names, either create a new Excel Workbook or open any existing workbook in the folder and use the below formula in any cell:

      =SUBSTITUTE(CELL(“filename”),RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“@”,SUBSTITUTE(CELL(“filename”),””,”@”,LEN(CELL(“filename”))-LEN(SUBSTITUTE(CELL(“filename”),””,””))),1)),”*”)

    • This formula gives you the folder address and adds an asterisk at the end. Now you can copy paste (paste as value) this address in any cell (A1 in this example) in the workbook in which you are extracting the file names.
  3. Go to Formulas Tab –> Define Name
  4. In the New Name dialogue box, use the following details
    • Name: FileNameList (feel free to change this)
    • Scope: Workbook
    • Refers to: =FILES(Sheet1!$A$1)
  5. Go to cell B1 and enter the following formula

    =IFERROR(INDEX(FileNameList,ROWS($B$1:B1)),””)

  6. Drag this down and it will give you a list of all the file names in the folder

If you want to get all the files with a particular extension, just the change the asterisk with that file extension. For example, if you want only excel files, you can use *xl* instead of *