How to export file names into an excel file?

Amin2234 151 Reputation points


I want to export 1000 file names into a excel doc with 4 columns by the following instruction:
File-1.mp3 =>> column 1
File-2.mp3 =>> column 2
File-3.mp3 =>> column 3
File-4.mp3 =>> column 4
File-5.mp3 =>> column 1
File-6.mp3 =>> column 2
File-7.mp3 =>> column 3
File-8.mp3 =>> column 4
File-997.mp3 =>> column 1
File-998.mp3 =>> column 2
File-999.mp3 =>> column 3
File-1000.mp3 =>> column 4

All files ended with number at the end…sorted by numbers from 1 to 1000 (exactly like above)

Office Management
Office Management
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Management: The act or process of organizing, handling, directing or controlling something.
1,776 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,508 questions
Windows 11
Windows 11
A Microsoft operating system designed for productivity, creativity, and ease of use.
5,930 questions
0 comments No comments
{count} votes

Accepted answer
  1. Lz._ 8,971 Reputation points


    I wasn't expecting a big RAR file but the .txt file only
    Due to the structure (SublistNumber-FileNumber) of your file names I revised the query => Output:


    Updated workbook available here

4 additional answers

Sort by: Most helpful
  1. Lz._ 8,971 Reputation points

    Hi @Amin2234

    With Get & Transform aka Power Query

    1/ Download that Excel file

    2/ In a command prompt (CMD):

    • Switch the dir. where your .mp3 are stored
    • DIR *.mp3 /B > FileNames.txt

    2/ In Excel:

    • Open the downloaded file
    • Update cell F2 with the path + file name created at #2
    • Right-click on the green table > Refresh


  2. Lz._ 8,971 Reputation points


    As you experienced it's not DIR *.mp3 > FileNames.txt but DIR *.mp3 /B > FileNames.txt - my bad :( + sorry about that
    That said, I renamed my dummy files and without changing anything in the query I get the expected result:


    I currently have no idea why this doesn't work as expected on your side. Could you:

    • Upload & share (i.e with OneDrive, GoogleDrive...) your files.txt after a DIR *.mp3 /B > FileNames.txt?
    • Confirm your Excel (2016, 2019.... 365) release + Version & Build (Go to File > Account)

  3. Amin2234 151 Reputation points


    Could you please check this file too and let me know sort it? Must be sorted according to the alphabet and number.

    0 comments No comments

  4. Lz._ 8,971 Reputation points


    This looks like a never ending story... My last update on this case in this file