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)
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.
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.
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.
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.
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!
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 UA-cam. 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.
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?
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
Useful video, i need to done this one in macro can it is possbile. Example: every day in drives file will keep on chages on day to day basics, so i want to do a macro can you help me out with that one
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.
This is Simply brilliant! however i have one question. Please can you help me by showing how can i extract all the file names which are stored in two folders. for example, I have One folder with 10 files and second folder with 10 files, what is the easiest way in getting the 20 file names.
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.?
This works great, but do you have a suggestion for gathering further information about the folders? Like sorting through the metadata and having it available to be inserted into a Pivot table?? That'd be amazing!
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.
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?
Hi, please make a video on how to search for specific folder in parent folder and again search for another sub folder in the previous sub folder through vba
At 5:18 is where you lost me. could not see how to "lock" the row and follow next steps. but then I read below on using command line and it worked nicely. but thank you for making the video. You may include some notes or something for the 5:18 mark.
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 🙏🏻
Thanx a lot, I have folder containing more than 5400 files. I tried and able to retrieve only 256 files name. what will be the next option to get full list?
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 ❤️❤️❤️❤️
Hi I followed the steps that you provided, but it is giving me a #name? I have doubled checked all the spelling in my formula. Does this work with all types of files like .jpg ? Thank you for your help.
Hi dear, this is kulwant; after you have copied the entire data to excel just click on column A1 and copy the starting string till last backslash "\" press command H paste the string in "find what" leave the "Replace with" now Hit: find next Replace if happy then find next Replace all 'WOW' no delimitations and hight gimmicks .................... Kulwant uncle any problem write back
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 :)
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?
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!!
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!
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.
Dear brother i use this formula its work but its not taking number from begning.its leaving the first 5 row and than picking the 6th row.please tell me the solution
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)
+Omkar Polekar
Worked Fine. But Flash Fill not worked
great
worked perfectly for me - the Trump Excel way didn't work for me. Thank you!
here is a video explaining the method in the above comment: ua-cam.com/video/JN65g-0k534/v-deo.html
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
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.
Used this to extract 132 files names. This video saved me an hour.
Yayyy... Glad it helped :)
Bravo. Can't believe this hidden trick is even existing in UA-cam.
I just wanted to say a big thank you for such a clearly explained video. Simply brilliant!
Wonderful video I loved it
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.
Thank you so much amit,this video helps me to copy all my wedding files name very quickly.thanks once again.
Cool tip! I used the Row function with no argument to increment the formula. That is a little easier.
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.
This is very useful, many thanks to you. I could not get any files when I use it with OneDrive!
you are a Genius.. many thanks for this precious video
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
Mr. Bansal: Fantastic!!! A top notch tutorial, made life very easy. Thank you!
great video, thanks. it is possible to get names of folders (not files)?
Thank you a lot. This will reduce my workload.
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.
Awesome trick. Thanks a ton. It worked like a charm
Excellent Video to enhance our understanding. However why should we need to get the seperate list of file names , when they are already in a folder?
So helpful thanks! How can this be done to list folders instead of files?
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!
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 UA-cam. 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.
Nice Information and really useful.
This was super helpful, thank you!
Thanks for the technique, its saved my time
This worked great for my needs. Quick and clear video, thank you Sir.
great tutorial, you just showed me another way of copying names of files.
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?
VERY NICE...WILL THIS AUTO-UPDATE ON ADDITIONS OF FILES IN THE FOLDER..???
This was extremely useful. I appreciate the clear presentation of the topic as well.
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?
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
Clear and precise. Thanks.
Dude, this is awesome!!
Very very useful stuff!!! Thank you!!!!
Awesome video! Thank you so much for helping me find a shortcut!
Useful video, i need to done this one in macro can it is possbile. Example: every day in drives file will keep on chages on day to day basics, so i want to do a macro can you help me out with that one
It was very precise and very well explained. Brilliant ! Thank you
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.
Thanks for Simple Video
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?
Thank you so mutch, Sir.
Great technique
Is there a way to only find the numbers of files in the folder or excel sheets in the the folder?
You are amazing, man! Thanks!
This really helpful!! Thank you so much!!
your video are very helpful and thank to you.
Very good tutorial. Thank you very much
Very good. Thanks.
Awesome... is it possible to get the file creation date
This is Simply brilliant! however i have one question. Please can you help me by showing how can i extract all the file names which are stored in two folders. for example, I have One folder with 10 files and second folder with 10 files, what is the easiest way in getting the 20 file names.
Very nicely explained, and useful. Thank you.
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.?
this is a top video, can you do it with google sheets?
Thank you brother
Good tutorial
Thank you again.
This works great, but do you have a suggestion for gathering further information about the folders? Like sorting through the metadata and having it available to be inserted into a Pivot table?? That'd be amazing!
I got it figured out. Thank you for the response.
Hi, how did you dit it? did you manage to do it with VBA? or with excel?
You saved my team hours of work. Thanks for the video :)
Fantastic video my friend, a great help
Thank you so much...you saved me hours and hours processing thousands and thousands of files for my thesis....THANKS!
Thanks my dear. I have a question if I want to get All file names in folder and sub folder ? How ?
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.
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?
You may just have saved my job with this! Thank you so much you've found yourself another subscriber!
Hi, please make a video on how to search for specific folder in parent folder and again search for another sub folder in the previous sub folder through vba
Please also guide to get file name list with specific file format
Thank you so much sir! you're a life saviour.. god bless you
Hi, thank you for this video its very helpful,but is there a way to make it make it read folders also
At 5:18 is where you lost me. could not see how to "lock" the row and follow next steps. but then I read below on using command line and it worked nicely. but thank you for making the video. You may include some notes or something for the 5:18 mark.
how to lock.. i didnt understood that
Great Bro I used your method and have easily created the list of my files. thank you so much
You are a life saver good sir. Thanks
Sumitji, how can we prepare a seperate list of all songs in excel from a pen drive or CD. Kindly explain.
Really helpful
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 🙏🏻
Thanx a lot,
I have folder containing more than 5400 files. I tried and able to retrieve only 256 files name. what will be the next option to get full list?
Thank you so much 💓
Thank you for this
Glad you found the video helpful 🙂
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 ❤️❤️❤️❤️
Hi
I followed the steps that you provided, but it is giving me a #name?
I have doubled checked all the spelling in my formula.
Does this work with all types of files like .jpg ?
Thank you for your help.
Hi dear, this is kulwant;
after you have copied the entire data to excel
just click on column A1 and copy the starting string till last backslash "\"
press command H
paste the string in "find what"
leave the "Replace with"
now Hit:
find next
Replace
if happy then
find next
Replace all
'WOW' no delimitations and hight gimmicks
.................... Kulwant uncle
any problem write back
Brilliant!!! It is very helpful. Thank you very much.
Thank you very much, just what I was looking for :)
WOW! great tips thank you.
Great really it helps. 👍
Thank you very much.You help me a lot!
thank youuuuuuuuuuuuuuuuu very much
Hi Sumit, I have multiple folders in a folder, how do we list files for all those together? Thanks in advance.
Extraordinary Video.Thank you so much.
Wow
Thank u so much
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 :)
Thank you so much for your great video
Thanks man.
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?
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!!
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!
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.
Just great, very simple and easy to manage without programming!!! :-)
Great video. I have subscribed.
Can we see the latest file name by making some changes to the formula
sir ye to alphabatical sequence se import kr rha hai agr datemodified sequance se imort krne ho to kese krende?
Dear brother i use this formula its work but its not taking number from begning.its leaving the first 5 row and than picking the 6th row.please tell me the solution
Big thanks, just one for question if we have subfolder? please advise
thx for your great video