How to Get the List of File Names in a Folder in Excel (without VBA)

Рет қаралды 321,829

9 жыл бұрын

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):
trumpexcel.com/list-of-file-n...
Free Excel Course - trumpexcel.com/learn-excel/
Paid Online Training - trumpexcel.com/excel-training/
Best Excel Books: trumpexcel.com/best-excel-books/
⚙️ Gear I Recommend:
Camera - amzn.to/3bmHko7
Screen Recorder - techsmith.z6rjha.net/26D9Q
USB Mic - amzn.to/2uzhVHd
Wireless Mic: amzn.to/3blQ8uk
Lighting - amzn.to/2uxOxRv
Subscribe to get awesome Excel Tips every week: kzfaq.info...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial

Пікірлер: 245

@omkarpolekar 8 жыл бұрын

There is a better way to do it. hit Ctrl + A (To selct all) in the folder which contains all files/folders the list of which you want. Press Shift & right click on any of the files/folders. click on 'copy as path' - Go to excel 2013 or later version - paste in cell A1 there. It will paste the whole path of all selected files in column A. In the cell B1 write only the file name of the first file/folder of your list. In cell B2 use Flash fill option or simply hit ctrl + E which will generate all the file names of the whole file/folder list. (If it doesn't then do same for B2 also & then press ctrl + E in B3 which will bring up the desired result)

@vamanamurthy5908 8 жыл бұрын

+Omkar Polekar Worked Fine. But Flash Fill not worked

@muhammadaminsarfraz6525 7 жыл бұрын

great

@playtone229 6 жыл бұрын

worked perfectly for me - the Trump Excel way didn't work for me. Thank you!

@MichaelDewTechTips 6 жыл бұрын

here is a video explaining the method in the above comment: kzfaq.info/get/bejne/gLRmaMpeldGZY2g.html

@pbhat99 6 жыл бұрын

This is easy to to use and works without error, but it will not update when we have added more files or when we changed our file names

@Fabiano2265 9 жыл бұрын

I just wanted to say a big thank you for such a clearly explained video. Simply brilliant!

@sleepot_ 7 жыл бұрын

Thank you! I looked all over for help with this and your method, though long and winding as it is, is the only one that actually worked on my computer without issues.

@safaboi203 7 жыл бұрын

Thank you so much...you saved me hours and hours processing thousands and thousands of files for my thesis....THANKS!

@paulmurray4464 5 жыл бұрын

This was extremely useful. I appreciate the clear presentation of the topic as well.

@user-is4qh3dj4j 2 жыл бұрын

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.

@nboorla 9 жыл бұрын

You may just have saved my job with this! Thank you so much you've found yourself another subscriber!

@sitaramarajusagi7334 6 жыл бұрын

I am immensely thankful for this video with great information.. I have many files named in Telugu Language and I wanted to have a database of files I had on different subjects in Telugu. This video Thanks once again.

@shushanknaik683 7 жыл бұрын

Thank you so much amit,this video helps me to copy all my wedding files name very quickly.thanks once again.

@uavrecon9233 7 жыл бұрын

This worked great for my needs. Quick and clear video, thank you Sir.

@madhavichowdhary 9 жыл бұрын

It was very precise and very well explained. Brilliant ! Thank you

@joem112 9 жыл бұрын

Cool tip! I used the Row function with no argument to increment the formula. That is a little easier.

@shambhalachakra4856 2 жыл бұрын

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 :)

@WildanKurniadiCode 6 жыл бұрын

Thank you so much sir! you're a life saviour.. god bless you

@AnTiViRuS231195 Жыл бұрын

Hey man, great video it will help me a lot in my work, Quick note, you can get the folder path by right click on it and choose properties, and it will show you the folder path Thanks you for your video 🙏🏻

@premchandgada2735 7 жыл бұрын

Very nicely explained, and useful. Thank you.

@lexhoge 8 жыл бұрын

Just great, very simple and easy to manage without programming!!! :-)

@rhythmapprentice 8 жыл бұрын

Awesome video! Thank you so much for helping me find a shortcut!

@emadkawekji8820 7 жыл бұрын

Brilliant!!! It is very helpful. Thank you very much.

@castillelarkin 4 жыл бұрын

AMAZING!!!!! Note, for some reason, I had to use the semicolon instead of the comma to separate the formula calls. Maybe because of my language settings? In any case, thanks so much!

@rocky20717 8 жыл бұрын

Extraordinary Video.Thank you so much.

@yahya-gt5lw 6 жыл бұрын

Thank you brother Good tutorial Thank you again.

@bubbachunk6168 8 жыл бұрын

Working 100% perfect thank you very much!

@zm2813 7 жыл бұрын

Hello and thank you very much for this video. If i would want to rename files in the folder consistently, can you please recommend the ways to do it?

@alexsepel6167 4 жыл бұрын

Thank you very much, just what I was looking for :)

@thezerked 5 жыл бұрын

You are a life saver good sir. Thanks

@galiakoti 7 жыл бұрын

Great Bro I used your method and have easily created the list of my files. thank you so much

@ParamveerSS 7 жыл бұрын

Mr. Bansal: Fantastic!!! A top notch tutorial, made life very easy. Thank you!

@ekeneduaka6888 8 жыл бұрын

great tutorial, you just showed me another way of copying names of files.

@kaliarajan 7 жыл бұрын

Wonderfully explained is clear speech.......... Quite useful to me....

@allarousan8041 5 жыл бұрын

wonderful, amazing, & useful :) Many many thanks for help

@CavanMitchell 4 жыл бұрын

You saved my team hours of work. Thanks for the video :)

@jmoney0448 9 ай бұрын

This was super helpful, thank you!

@jancopier 9 жыл бұрын

Thanks, this was really usefull! Now this is giving a list of files in a certain folder. How would I get the folders in this directory as well?

@nadendla8861 9 жыл бұрын

Thank you so much.. it's really helpful :)

@wrthegreat355 Жыл бұрын

Clear and precise. Thanks.

@ratheesh.m 4 жыл бұрын

Thanks for the technique, its saved my time

@GeorgeSee 2 жыл бұрын

you are a Genius.. many thanks for this precious video

@arasdezay 5 жыл бұрын

Thank you so much for your great video

@khurramali42 7 жыл бұрын

WOW! great tips thank you.

@StationOnLife 8 жыл бұрын

A MILLION THANKS to you for doing all these such wonderful, excellent, extremely helpful to people like me. I have been looking and wanting to be able to something like this for a long time, and could not find any tutorial on KZfaq. And also, I didn't know how to ask the question. So, this tutorial video has been a "life saver" for me. In addition, your clear, detail, helpful explanation and your right speed in your tutorial are greatly appreciated and acknowledged. Thank you so much again. (I just have a question or, perhaps, is a request. Is there a shortcut or an easier way after getting all file to the worksheet, is there an easier way for me to insert or create a hyperlink. The way I am doing it now is the old fashion way which is click Insert Hyperlink icon then select the file and so on and so on.). Thank you.

@georgeradu2165 3 жыл бұрын

You are amazing, man! Thanks!

@okcomewithme1060 6 жыл бұрын

Thank you very much.You help me a lot!

@XiongSir 2 жыл бұрын

Bravo. Can't believe this hidden trick is even existing in KZfaq.

@Doktor47 4 жыл бұрын

Fantastic video my friend, a great help

@olegtran5380 5 жыл бұрын

Thank you for video, very helpful

@jassemaldhaheri6456 6 жыл бұрын

Hi, thank you for this video its very helpful,but is there a way to make it make it read folders also

@ziyonjacobvv6017 Жыл бұрын

Used this to extract 132 files names. This video saved me an hour.

@trumpexcel Жыл бұрын

Yayyy... Glad it helped :)

@terryritivoy6220 7 жыл бұрын

This formula works great and is a huge time saver. I was hoping to expand its usefulness even further. When using this formula is it possible to include "Hyperlink" so that when the list is created the items of the list become live links in the spreadsheet referencing the files location in the folder? The links would be used to launch video files from the spreadsheet. If so this would really help me with my project. Thank you.

@Musician25 3 жыл бұрын

Very very useful stuff!!! Thank you!!!!

@alyzzaazcueta 3 жыл бұрын

This really helpful!! Thank you so much!!

@LaHuachitarica 6 жыл бұрын

Thank you this works great!

@saDikus1 9 ай бұрын

Awesome trick. Thanks a ton. It worked like a charm

@mohdkhairilakub8064 3 жыл бұрын

Thank you a lot. This will reduce my workload.

@playchompoo 2 жыл бұрын

Big thanks, just one for question if we have subfolder? please advise

@rockdsouza5812 5 жыл бұрын

very useful ..thanx bro

@Branchiesthlm 9 жыл бұрын

Awesome! This is exactly what i need :) almost... Is there a way to include the names of folders as well? I also found that in some folders, there doesn't seem to be any logic to the order in the generated array. Why isn't it always alphabetical?

@erhanyildirim2242 6 жыл бұрын

Very good tutorial. Thank you very much

@erhanyildirim2242 6 жыл бұрын

Very good. Thanks.

@qzddzdwx 7 жыл бұрын

So good ! Thank you very much Trump Excel for the time-saving tips !

@reflectadditive7102 Жыл бұрын

Thanks for Simple Video

@mahmoudnagy7109 7 жыл бұрын

thx for your great video

@michaelsamchuk1819 2 жыл бұрын

So helpful thanks! How can this be done to list folders instead of files?

@vaibhavkulkarni3285 3 жыл бұрын

Nice Information and really useful.

@MohAboAbdo 3 жыл бұрын

Thank you so mutch, Sir.

@shreepatil4917 8 жыл бұрын

Hi, Many Many Thanks !!!!!!!!!!!!!! Just cannot express in words How great it had helped me. Thanks !!!!!!!!!!!!!!!!!!!!!!!!

@alexsemenchuk 5 ай бұрын

Dude, this is awesome!!

@RecordLifeMedia 5 жыл бұрын

will this work for subdirectories? I.e. folder has several folders with files, I would like to get a folder and files. I tried following it but I get nothing. Thanks.

@yiannoswhatever 9 жыл бұрын

I wanted to just congratulate you for the really good explanatory clip! Thakns! I just have one question: all the above work perfectly, if you want to create a list of files, but what if you want to create a list of folderrs within a folder? How can it be done?

@tajdarsiddiqui6230 4 жыл бұрын

your video are very helpful and thank to you.

@Dawnkey 6 жыл бұрын

Thank you!

@somaiyamilind 2 жыл бұрын

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

@abdulrahmanalthunayan9579 2 жыл бұрын

This is very useful, many thanks to you. I could not get any files when I use it with OneDrive!

@BeatrixSontag 8 жыл бұрын

Many Thanks! :) is there a way to do this also for folders within folders? I mean .. If I have a folder A with different files in it and also sub-folders within folder A .. is it possible to get something looking like a site-map with all levels? :) TIA!

@gayatrishaw5389 8 ай бұрын

Thank you so much 💓

@riteshchandel9368 6 жыл бұрын

Thank you man. You saved a lot of time and effort.

@deepaksharma3399 6 жыл бұрын

excellent Thanx a lot

@borecz161 7 жыл бұрын

Great video. I have subscribed.

@stockmarketforeveryone Жыл бұрын

Wonderful video I loved it

@mayurlohia342 3 жыл бұрын

Really helpful

@shivamkumar001 6 жыл бұрын

Can you please tell if there is a way to extract other information of files like date, authors, etc in other columns

@md.arifurrahman9850 5 жыл бұрын

Thank you so much...

@abdanomer 6 жыл бұрын

Great technique Is there a way to only find the numbers of files in the folder or excel sheets in the the folder?

@rspencer87 5 жыл бұрын

amazing video wow.

@mohanvaze1 Жыл бұрын

Thank you so much for the interesting tutorial. Earlier I was manually copy pasting the file names from the folder to excel sheet. This video is super useful. Just one question. Is it possible to create a link on all the file names in the list so that by just clicking on the file name in the list one can open the file in the folder? Please let me know. Thank you. Best regards.

@DmNetworks 6 жыл бұрын

thank you, how can you get rid of the extension?

@moseswahome5213 3 ай бұрын

Thank you for this

@trumpexcel 3 ай бұрын

Glad you found the video helpful 🙂

@devendrabain7748 5 жыл бұрын

Thank you very much sir Each time i view your videos, i learned from them. Today i learn to make list. Its an amazing experience. Love Trump Excel ❤️❤️❤️❤️

@girishingole4495 4 жыл бұрын

Great really it helps. 👍

@setheroth28092 8 жыл бұрын

I am using Excel 2016 for Mac. I followed your steps exactly, but I get a N/A error? Does the Files() function still work when defining names on a Mac? Any help you can offer is greatly appreciated. Thanks!!

@sandeepgoud7595 6 жыл бұрын

Thank you

@rajanishmanitripathiamazion 7 жыл бұрын

Thank You :)

@nishit6148 7 жыл бұрын

hi, good learning video however have one doubt. do we really need to create test file in same folder from where we want file names? I tried without that and it gave me same folder path. is there a way to do without saving test file in original folder?

@teoxengineer 2 жыл бұрын

Another way, select all files in folder (Cntr+A) and hold shift+alt+right click and copy as path and select excel sheet and then paste. Thats all, all files name will bring to sheet

@byronmcfarland6786 8 жыл бұрын

Now that we have this list of file names, can you show how to retrieve data from the workbooks using this list of names as the reference? Example you wanted the data stored in cell A1:A1 for each file.?