If you want to extract the file names of the files contained in a certain folder, put them in the worksheet for other uses, it is very troublesome to input them one by one or copy and past them. In fact, we can use the two excel functions FILE and INDEX together to extract the file name in a special folder and then paste the file name to an excel worksheet easily.
1. Excel Extract File Name In Folder Steps.
- First, open the Excel file, click the “Formulas” tab, click the “Define Name → Define Name” menu item in the Defined Names area.
- Input a name ( for example Get_File_Names ) according to your needs in the Name: input text box of the pop-up New Name window.
- Enter “=FILES(“D:\users\tom\documents\*.*”)” in the Refers to: input text box.
- Note: The quotation marks in the function are English quotation marks, and the content in the quotation marks is the absolute path of the folder where the file name is to be obtained.
- The syntax format of the FILES function: FILES(directory_text). Where directory_text specifies which directory to get the file name from, it must be an absolute path.
- The directory_text accepts the wildcard question mark(?) and asterisk (*). The ? sign matches any single character, and the * sign matches any sequence of characters.
- For example, if directory_text is “D:\users\tom\documents\*.doc“, it means that all the documents with the extension “.doc” are returned.
- The value of directory_text can be obtained by the following method, open the folder where the file name is to be obtained, click in the address bar of the folder so that the absolute path of the folder is selected, press CTRL + C to copy it, and press CTRL + V to paste it.
- Next, enter “=INDEX ( Get_File_Names , ROW(A1))” in cell A2 of the worksheet, and then pull down until an error value appears which means finish.
- If the input in the name Refers to: is “=FILES(“D:\users\tom\documents\user*.*”)“, you can only extract all files that contain the word “user” at the beginning of the file name.
- Of course, you can also apply MID, LEN, and other functions on the extracted file name to extract the pure file name without the extension.
- For example, enter “=MID(A2,1, LEN(A2)-4)” in cell B2 and drag it down to fill, so that the required pure file name is extracted.