How to rename 5000+ files using Excel

p_grad

New Member
Joined
May 5, 2010
Messages
41
Hi all,

I have navigated to a folder in DOS in order to copy and paste file names into Excel in order to rename appropriatley before archiving.

When I paste into Excel I can only retrieve the last 296 files. Is there a limit on how much data I can copy using this method?

I'm using instructions I found on the internet (See below) but if anyone can suggest a better way of doing this I would be extremly grateful.

Step 1: Type cmd and switch (using cd command) to the directory that contains the files you want to rename in bulk.

Step 2: Type dir /b to see a skeleton list of all files in the current directory. Copy that file list to the clipboard by selecting Mark from the contextual menu.

Step 3: The fun starts now. Fire your copy of Microsoft Excel or Google Docs Spreadsheets and paste those file names into the first spreadsheet column.

Step 4: In the blank adjacent column, add a corresponding Excel function for instance, use SUBSTITUTE to change specific text in the filenames, use CONCATENATE() with DATE() if you want to add date to the filename, etc.

*For more complex criteria, you may want to put the file extensions in a separate column by splitting the file name using period (.) as the delimiter.

Step 5: Your source filenames are now in column A while the Destination files are in column B we will now convert this into a DOS rename command.
In Column C, type the following Excel formula and your command is ready to be executed.
=CONCATENATE(ren ,A1, , B1)

Copy paste the same formula across all cells in column C for which you have corresponding values in Column A or B.
We are almost done. Copy all the values from column C to the clipboard and paste them inside the DOS window or better still, put them inside a new text file and give it a name like rename.bat.
Execute and all files that match the criteria are renamed instantly
 
What I'm saying is you don't need DOS for this at all. You can write a macro in VB or VBA [from within Excel] that will let you rename files in any folder you desire. Use Excel Help to search on FileSystemObject.
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try this:
Code:
Sub x()
    Const sPath     As String = "C:\myPath\"    ' don't forget the trailing "\"
    Const sExt      As String = ".xls"
 
    Dim sFileInp    As String
    Dim sFileOut    As String
    Dim sDate       As String
    Dim iPos        As Long
 
    sFileInp = Dir(sPath & "*.xls")
 
    Do While Len(sFileInp)
        If sFileInp Like "*##.##.##" & sExt Then
            iPos = InStrRev(sFileInp, sExt) - 8
            sDate = Replace(Mid(sFileInp, iPos, 8), ".", "/")
            sFileOut = Left(sFileInp, iPos - 1) & Format(CDate(sDate), "yyyymmdd") & sExt
            Name sPath & sFileInp As sPath & sFileOut
        End If

        sFileInp = Dir()
    Loop
End Sub
 
Upvote 0
If you're after a formula approach, try the following:
Code:
=LEFT(A1,FIND(".",A1)-3)&"20"&MID(A1,LEN(A1)-5,2)&MID(A1,LEN(A1)-11,2)&MID(A1,LEN(A1)-8,2)&RIGHT(A1,4)
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top