How to rename 5000+ files using Excel

[p_grad]

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

 

[njimack]

take a look at http://www.glynnconsulting.co.uk/RenameFiles.zip

 

[p_grad]

This looks perfect.

Thanks very much!

 

[p_grad]

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]

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]

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]

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.

 

[zakasnak]

take a look at http://www.glynnconsulting.co.uk/RenameFiles.zip

This is an OSM utlity. Can you help me with a formula?

All of my file names end with the date mm.dd.yy (ex. store_buybacks_we_12.20.08.xls)

I would like to change them all to yyyymmdd (ex. store_buybacks_we_20081220.xls)

I see that I can use formulas in this utility, but I don't know how to start....

 

[doofusboy]

look into the FileSystemObject......it's designed to allow you to do all sorts of file operations from within vba

 

[zakasnak]

look into the FileSystemObject......it's designed to allow you to do all sorts of file operations from within vba

????