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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

p_grad

New Member
Joined
May 5, 2010
Messages
41
Hi,

This is a great tool, unfortunatley I was not clear in my question and it is not quite what I was after.

It is infact the folders I want to rename. Each of the 5000+ folders has the follwing format:

'Smith John 123456' which I want to change to 'Smith-John-123456'.

Then within each folder are a few documents such as '0809' 0910' and '1011' which I will want to change to 'Smith-John_123456 0809'.

As far as I can see the file renaming will be easy using this tool but still a repetitive task, its the folder renaming I'm hoping to find an answer for.

Hope that makes more sense.
 

Rekd

Banned
Joined
Apr 28, 2010
Messages
136

ADVERTISEMENT

There is a progam that will do it for you and it's free. It's called FreeCommander and has a multi-rename tool where can use variables, serialization, search/replace (great for changing 'Smith John 123456' to 'Smith-John-123456' by searching for " " and replacing with "-".)
 

p_grad

New Member
Joined
May 5, 2010
Messages
41
Hi,

Thanks for the link. Its looks exactly what I need!

Unfortunatley my employers I.T deptartment won't allow me to download software and would rather we spend hours upon mind numbing hours doing this manually.

Cheers
 

p_grad

New Member
Joined
May 5, 2010
Messages
41

ADVERTISEMENT

Hi rekd and njimack

I found a way to do it after all

Create a bat file by navigating to the directory using cd then use dir >> foldernames.csv to export into a csv

Then import into Excel and do whatever you need to create the new folder names

Put The 'ren' (rename) command in front of of the new name

<TABLE style="WIDTH: 657pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=876 border=0><COLGROUP><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 35pt; mso-width-source: userset; mso-width-alt: 1718" width=47><COL style="WIDTH: 132pt; mso-width-source: userset; mso-width-alt: 6436" width=176><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 277pt; mso-width-source: userset; mso-width-alt: 13494" width=369><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 118pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=158 colSpan=3 height=20>Imported CSV Data</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 132pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=176>Old Name</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 130pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=173>New name</TD><TD class=xl64 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 277pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=369>DoS Command</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>ADAMS</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Matthew</TD><TD class=xl63 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">469 </TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">"ADAMS Matthew469 "</TD><TD class=xl65 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ADAMS-Matthew-469</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent"> Ren "ADAMS Matthew 469" ADAMS-Matthew 469</TD></TR></TBODY></TABLE>

Copy the Dos Command into notepad and save it as a bat file (This bat file must run from the directory in which the file resides to work)

Thanks to everyone who helped with this.
 

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
look into the FileSystemObject......it's designed to allow you to do all sorts of file operations from within vba
 

Watch MrExcel Video

Forum statistics

Threads
1,108,937
Messages
5,525,732
Members
409,661
Latest member
pprabha

This Week's Hot Topics

Top