Autofill number and text series

Hello,

you could use a formula instead of auto-fill. Something along the lines of

="sound: IUB 3 ("&ROWS($A$1:A1)&").mp3"

Copy down as far as required, then copy the column and use Paste Special > Values to paste only the results and remove the formulas.

cheers, teylyn

If you are comfortable using CMD.exe you could go to the Command Prompt, navigate to the folder with the mp3s and enter this command

dir *.mp3 /b>music.txt

This will create a text file in the folder with your music that lists all the files one under the other. Import into Excel and when prompted for delimiters etc just click next and finish and you are done.

Another approach..

Try the following procedure to get a list of all files from the folder:

1. In the new workbook, press Ctrl+F3 > New. In the name box, type FL
2. In the Refer to box, enter =FILES("C:\Folder\where\MP3 Files are\*.mp3")&T(NOW()).
3. Click on OK and Close

In cell A3 of sheet1, enter the following formula and copy down

=IF(ISERROR(INDEX(FL,ROW()-2)),"",INDEX(FL,ROW()-2))

If you add another Excel file to the Survey report folder, just press F9 anywhere on sheet1 and the file will appear in the list.

Courtesy : Bob Umlas

Right. If we're after high-end, here's another one:

Install the Power Query add-in from Microsoft, then click the Power Query ribbon > From File > From Folder. Navigate to the folder and click OK.

Remove the columns you don't need. Create a column that combines the file name and the text you want. Apply and close.

Now, when the data in your folder changes, you can refresh the Power Query to reflect the current folder content.