Often enough, a bunch of files accumulate over time. For example, in a company’s accounting department, many invoices accumulate over the course of a year, all of which end up in a folder in which one quickly loses track.
How nice would it be to simply name the files in a Excel worksheet to be able to read and process!
In fact, you can Excel relatively easy to do. To stick with the accounting example: you can Excel With just a few clicks of the mouse, you can simply transfer all file names in a folder to a worksheet. Now you can even extract individual parts of the file name with little effort and paste them into another cell so that you can also analyze the flood of data.
Filenames in a Excel-Apply worksheet
To add the names of any number of files to the Excel worksheet, you proceed in a similar way to when you .csv files want to transfer to a worksheet.
First, switch to the “Data” tab at the top and select the “Retrieve data” drop-down menu. Under “Retrieve data” you select the option “from file” and here the option “from folder.” Now a window opens in which you can find the folder in which the files you want to retrieve are located.
Clicking on “Ok” opens the “Excel Power Query window. Here you can see the retrieved data and, if necessary, combine it with files from other folders by clicking on “Combine”.
When you have selected all the dates you want in Excel want to insert, confirm the selection by clicking on “Transform data.”
Edit data with the Power Query Editor
In the next step, you can edit the data in the “Power Query Editor” as you wish. Here you can set, for example, which data you want later Excel wants to view the worksheet. For example, if you do not want to include the data from a column, you can simply exclude it by clicking on “Remove columns”.
By clicking on “Save & Load” you confirm the selection and add the data to the active one excel worksheet one.
Caution: once you have inserted the data, you cannot undo it!
The data in Excel process
If you put the data in Excel and want to process it further, you have the problem that you have to change the file names in Excel Power Query Editor can be easily disassembled.
So if you want to split the file names, you have to work with functions like PART, LEFT, or RIGHT here. We also have a detailed tutorial for this.
As a quick example:
In this example, to display the date in one cell and the invoice number in the other cell, use the PART function. This allows you to transfer parts of text in one cell to another cell.
For example, to enter the date in another cell, you enter the function
=PART(A2;1;10)
a. Where PART is the function, A1 is the cell to read the data from, 1 is the first digit (or letter in one case) to extract, and 10 is the number of characters to extract.
Now the date 11/13/2019 appears individually in a cell. The function can simply be transferred for the other data.
If you apply this function to the other parts of the filenames as well, you can analyze a large number of filenames in no time at all.
