How to Get the List of File Names in a Folder in Excel (without VBA)
HTML-код
- Опубликовано: 28 сен 2024
- 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...
Free Excel Course - trumpexcel.com...
Paid Online Training - trumpexcel.com...
Best Excel Books: trumpexcel.com...
⚙️ 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: www.youtube.co...
Note: Some of these links here are affiliate links!
#Excel #ExcelTips #ExcelTutorial
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: ruclips.net/video/JN65g-0k534/видео.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
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?
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 RUclips. 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.
This was extremely useful. I appreciate the clear presentation of the topic as well.
Cool tip! I used the Row function with no argument to increment the formula. That is a little easier.
You may just have saved my job with this! Thank you so much you've found yourself another subscriber!
Thank you so much amit,this video helps me to copy all my wedding files name very quickly.thanks once again.
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.
Thanks for the technique, its saved my time
This worked great for my needs. Quick and clear video, thank you Sir.
you are a Genius.. many thanks for this precious video
It was very precise and very well explained. Brilliant ! Thank you
Thank you brother
Good tutorial
Thank you again.
VERY NICE...WILL THIS AUTO-UPDATE ON ADDITIONS OF FILES IN THE FOLDER..???
Very very useful stuff!!! Thank you!!!!
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.
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!
Thank you a lot. This will reduce my workload.
Sumitji, how can we prepare a seperate list of all songs in excel from a pen drive or CD. Kindly explain.
Very nicely explained, and useful. Thank you.
You are amazing, man! Thanks!
This was super helpful, thank you!
sir ye to alphabatical sequence se import kr rha hai agr datemodified sequance se imort krne ho to kese krende?
Wonderful video I loved it
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
Awesome video! Thank you so much for helping me find a shortcut!
great tutorial, you just showed me another way of copying names of files.
great video, thanks. it is possible to get names of folders (not files)?
Thank you so much sir! you're a life saviour.. god bless you
Thanks my dear. I have a question if I want to get All file names in folder and sub folder ? How ?
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.?
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.
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.
Dude, this is awesome!!
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?
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.
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?
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.
Awesome... is it possible to get the file creation date
Clear and precise. Thanks.
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 ❤️❤️❤️❤️
Great Bro I used your method and have easily created the list of my files. thank you so much
Thanks for Simple Video
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 🙏🏻
Brilliant!!! It is very helpful. Thank you very much.
Thank you so much...you saved me hours and hours processing thousands and thousands of files for my thesis....THANKS!
You are a life saver good sir. Thanks
Hi, thank you for this video its very helpful,but is there a way to make it make it read folders also
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
Does this only works to identity Excel or Docs file, what if I want to get PDF or Sub Folders Path
This really helpful!! Thank you so much!!
Nice Information and really useful.
Fantastic video my friend, a great help
Extraordinary Video.Thank you so much.
Just great, very simple and easy to manage without programming!!! :-)
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
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
how would one make this work with files stored on sharepoint onedrive (not local)
Thank you man. You saved a lot of time and effort.
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
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?
this is a top video, can you do it with google sheets?
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?
Hi Sumit, I have multiple folders in a folder, how do we list files for all those together? Thanks in advance.
your video are very helpful and thank to you.
The formula also displays all the files other than excel files... How can we get only the excel file list
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
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!!
Working 100% perfect thank you very much!
Thank you very much.You help me a lot!
what i need is i want to know if the value of cell'file name' is existing in a folder by not using Cntrl F..
thanks in advance
How to save define name, because every time I open file I has to define formula again, please help
WOW! great tips thank you.
Thank you so much for your great video
wonderful, amazing, & useful :)
Many many thanks for help
Really helpful
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!
Cmd
dir/b >txt
and over...
1s text doc with all files then
ctrl A crtl V in exel and over...
HI, Can you help why it's not working in 2010 version. I am not getting the formula when I pressed F3
Hi sir difine name ek baar set karne se nahi horaha he jab bi file open karta hun to pir se difine name karna hota he aisa kyun...????
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
where do I get the name of the folder from? Or are you referring to the location of the folder? if so, I know where to get it. thanks.
Thank you so much 💓
thx for your great video
Hi sir
How is possible that every day data automatically copy from one sheet to other sheet
Without VBA only using the excel formula
Thank you this works great!
Thank you so much.. it's really helpful :)
Video is ok but in that folder we have date wise sub folder means how can we do it
Great video. I have subscribed.
Thank you for this
Glad you found the video helpful 🙂
Needed a tip on calculate (similar name) number of pdf files in a folder in excel
For example :
I have a data of pdf files with names like
01, 01 xyz, 01abc, 02, 02abc ,02 qwer, 02llig etc
So here, I want to note down the number of files having 01 and 02 and so on in excel.
Copy path trick will just give names in plain text but won't give number of files with similar name automatically.
Can anyone help me with it?
Is there a way you can do this with folder names?
Wow
Thank u so much
Hi Sumit,
good video, I am trying to solve a problem.
I have 200+ data file from stock exchange with 3000+ records each.
Exchange is generating one file per date, and file name contain date like EQ01012016.xls and so on. this date is missing inside the file and want to add date in the files with all its records for whole year. is there and smarter way then copy paste.
thanks
Great really it helps. 👍
very useful ..thanx bro
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?
will this work for files on One Drive?
Is there a way to get the timestamp as well?
Excellent