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
 

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
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.
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
Office Version
  1. 2010
Platform
  1. Windows
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
 

njimack

Well-known Member
Joined
Jun 17, 2005
Messages
7,764
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,939
Messages
5,525,739
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top