In this video, I will show you how to get a list of file names from folders into Excel (without the use of any VBA or coding).
You can use this technique to fetch all the file names from a folder and list that in Excel.
This Excel trick uses an old Excel Function – FILES.
FILES function can be used to get the list of all the file names (or specific file names) in a specific folder in Excel. It an old excel function that does not work in the worksheet, but still works in named ranges in Excel.
All you need is this formula with the folder address.
To get the folder address, save the excel file in the same folder and use this formula – =SUBSTITUTE(CELL(“filename”),RIGHT(CELL(“filename”),LEN(CELL(“filename”))-FIND(“@”,SUBSTITUTE(CELL(“filename”),””,”@”,LEN(CELL(“filename”))-LEN(SUBSTITUTE(CELL(“filename”),””,””))),1)),”*”)
One of the good things about this method is that it allows you to get specific files from the folder. For example, if you want to only get Excel files or only Word Files, or Only files with a specific extension, you can do that using this method.
Here is a step-by-step written tutorial (which also shows how to do this using VBA):
Free Excel Course –
Paid Online Training –
Best Excel Books:
⚙️ Gear I Recommend:
Camera –
Screen Recorder – techsmith.z6rjha.net/26D9Q
USB Mic –
Wireless Mic:
Lighting –
Subscribe to get awesome Excel Tips every week:
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
Watch more new videos about Excel Office | Synthesized by Mindovermetal English
Great Video. Any chance you can show how we can create the link to actual file names which are generated? That would be a great help. Thanks
Such great advise and to the point! Thank you so much for sharing your expertise. This will help me so much of my time from now. Greatly appreciated 🙂
Hello again. I found this video so Helpful! I'd like to add another (old) method of getting a list of Folders AND files in a given directory. For those who'd like to venture into the Windows "Command Prompt". This method will create a TEXT file in the directory of your choosing – from where, you can Open the Text File in Excel, and (have your full listing, to be able to manipulate sid listing as you wish in Excel.): 1. Use Widows File Explorer to navigate to the directory containing the folder (and/or group of folders and files) you want to appear in your listing. 2. In the Address Bar in File Explorer -type "cmd" and press Enter. (this opens the Command Prompt (old DOS) window at the "prompt" in the folder you selected. 3. Type: dir /A:D /B /S> FoldersAndFilesList.txt and press Enter. (This command/action, generates a complete listing 'folder structure' into the txt file – as named above (you can choose to name the file as you wish, of course.) 4. Type "Exit" in the Command Prompt Widow – to close the (DOS)/Windows Command Prompt pop-up. 5. Use File Explorer and look in your selected Folder for the file you just created [FoldersAndFilesList.txt]. You can open this with Word, any Text File Editor, or directly with Excel. <This is the complete listing of your Folders and Files structure in the Directory. [ CAREFUL: This can take a while if you have a very large directory with many sub-folders and files contained therein.] * Note: I did not go into any explanations of what the Command/Switches mean… i.e., the "dir", the /A /B /S etc.. it would take a longer conversation to those no familiar with the (old) DOS commands and it use. // IF you are curious about DOS & the Command Prompt "commands" and their use: You can type "HELP" at the Command prompt window and review for your self. * WARNING: Don't mess with commands if you're not sure of what you're doing here. Enter at your own risk. or Consult with a person who knows these commands and "Old-School" DOS.
Brilliant! This is the most – straight-forward method of listing files in a folder and the biggest time-saver. If you ever get a chance to update (add to this method), I would love to see this expanded to help create a "link" to the file itself in the workbook. So, the resulting workbook can be used as a 'true IDEX of the folder contents, and when a user opens the workbook, s/he can readily "open" a give file by just clicking on the 'link' within the Excel file.) /I've done 'this" many times over my year, – using different methods/tools; but Using the formulas/method in this Video, really saves a lot of work. Best Regards. and a big THANK YOU for posting this.
Hello sir. Your tutorial is amazing. Sir I have a doubt in excel. Please tell me how to remove duplicates words in same cell in same line.
example in cell A1 has some words
" MAA400 MAA400 NEFT TRANSFER".
here the words MAA400 are repeated twice . So I want remove duplicates words in same line in same cell.
WOW! This was so helpful! Thank you!!!
Thank you.
Awesome trick to list the files from folder.
I found another video in YouTube having quite easy trick to extract folder list.
https://youtu.be/vIEfdeavmh8
This is very useful, many thanks to you. I could not get any files when I use it with OneDrive!
Awesome… is it possible to get the file creation date
Really helpful
5:24
If I have different folders and files. How to list in one excel file? Pls advise. Thank you
Please also guide to get file name list with specific file format
why is my finding N/A
Thank you so mutch, Sir.
How to extract number of pages within these files, and show this in excel
How to save define name, because every time I open file I has to define formula again, please help
Does this only works to identity Excel or Docs file, what if I want to get PDF or Sub Folders Path
Why Am i Seeing "You have entered too many arguments for this function"????
Only Filename Displayed i need properties value automatically fill example one folder so many image here then the image name,width,height,image creation date visible on excell
Will this be dynamic?
You are amazing, man! Thanks!
This really helpful!! Thank you so much!!
Thank you a lot. This will reduce my workload.
Cmd
dir/b >txt
and over…
1s text doc with all files then
ctrl A crtl V in exel and over…
super information it is useful for me thank you