Rename Multiple Files Efficiently Using Excel or Google Docs
Renaming multiples files on your Windows computer is easy. Select all the files, press F2 and type some descriptive text. Windows will append a unique sequence number to each of the file names.
That’s a fairly quick solution but not very flexible because you don’t get to specify any choices or criteria.
For instance, how do you append the current date to the filename ? Or you want to replace only certain words in the file names (like a spelling error)? Or you want files to follow a different renaming system like file-A.jpg instead of the default file(1).jpg.
To rename files in bulk more efficiently, you can either learn some complex shell scripting language (SED, AWK, Perl) or switch to something more simple - a spreadsheet.
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.
Subscribe in a reader
Ever heard of scripting ? This is by far the most convoluted way of renaming files
Rename Master is free utility which does this and more very easily.
There are tools available to do all this and more like changing extensions, operating on various variable parameters and they work in nested subdirectories as well. I used one such tool 3 years back to rename mp3 files on my PC to some other extension :).
LOL. Nice. I didn’t realize it could be done this way.
Clever. Nicely done.
For a person not comfortable with scripting, but comfortable in Excel, this would be a great technique.
To #1, #2, #3 I think you’re missing the point.
There are a lot of people who work on locked-down computers that won’t allow them to install third party software or run scripts. Group policies rightfully prevent this.
Those people still have need to bulk rename at time, and what is outlined above is a way to do this using nothing more than a command line and Excel.
You may not need such knowledge, but other people do, and this will save them a lot of time.
Hi dear admin,,, there s one correction in your code
You have given =CONCATENATE(”ren “,A1, ” “, B1) but i guess the correct one is … note the subtle difference in the order of the the quotes (” “) there is a change in the start(”) and the end quote(”). the correct one would be… =CONCATENATE(”ren “,A1, ” “, B1) . i hope u hav got the difference…
hemant
My file names have spaces in which this little trick doesn’t like. What can I do?
Instead of the =CONCATENATE formula, try this:
=”ren ” & A2 &” “&A3
For those of who are too lazy or can’t spell Concat….
I use this application to rename files. Easy and free.
Oscar’s File Renamer
http://www.mediachance.com/free/renamer.htm
Sweet trick, I’d REALLY LOVE to do this with ID3 tags in MP3s. I know there are tools out there for ID3s, I swear I’ve tried them all. I want a spread sheet with:
Path, Filename, Artist, Name, Album, and NewPath, NewFilename…
That way I could have it move, rename, and tag. Guess I’ll have to write a script for that one.
I’m a programmer but I still find it easier to do this stuff with excel. It’s much easier to type a formula and fill down than write some script every time you want to rename a few files. Plus you can see the outcome in the next column and make changes if needed.
Very interesting.
I actually wrote a utility for this but this way makes a lot of sense!
You got my bookmark!
Good Technique…
Ever tried Total Commander?..That has got Multi Rename tool with many many more additional features.
In Step 2, we can also use a “>” symbol and a filename.
Example:
dir /b > filelist.txt
this is: the list generated by dir /b is not going to screen, but the file.
This is the stupidest, COMPLEX and makes junk tip.
Try Rename Master, Total Commander and they do this task in a jiffy.
For batch renaming files, I would recommend CKRename and Flex Rename.
If you are in Linux, KRename would do the trick.
Nice way to kill an ant using an axe! Export dir/b to a file and use Cntrl+H! Replace stuff as specified here in the example! Better get a textpad!
Yeah, that Excel trick is much more fun than a simple rename utility.
I have used it in the past, and it is great!
I like Metamorphose. It is the best free file AND folder renamer I have seen yet.
http://file-folder-ren.sourceforge.net/
I have tried 10-15+ rename utilities and none of them do what I need them to do, (including Metamorphose, which I just tried). I scan 100 to 200 images at a time and they end up with names like MyScan01, Myscan02, etc. I post these documents to the web and need to give them names like 001 C 1.0 Site Plan. Numbering the files is not the issue, its the unique name each one gets. I want to type the document names into a text doc, word doc, or excel spreadsheet and then rename the documents using it. I believe this could help. All those batch programs do not let you get names from another file.
grr, no spaces allowed in the names!
Brian,
I just tried the same thing as you are doing but it looks like windows will not allow those names.
I tried ren MyScan2.tif 002 DC101 Demolition Plan.tif and also rename MyScan2.tif 002 DC101 Demolition Plan.tif
I noticed it works fine if you add a dash or underbar instead of the spaces.
Anyone know a way around this?
To rename files that include spaces, just encapsulate the filenames in quotes. This can get confusing because a quote is a special character in Excel strings. You must use double quotes for each one that you actually want to display, so it looks like this:
=”ren “”"&A1&”"” “”"&B1&”"”"
which depending on what you have in A1 and B1, would give you:
ren “C:\program files\junk file.txt” “renamed file.txt”
which you can then copy into cmd (or even the start menu Run dialog if you include the full path). Sadly the “ren” command is not smart enough to also move/copy the file to a new directory, so there’s no point in putting a path in b1, though you could do something similar with xcopy.
If all you want to do is replace the underscores in a filename with spaces, no need for a separate column of filenames; just generate the new name and the rename command in the same cell:
=”ren “&A1&” “”"&substitute(A1,”_”,” “)&”"”"
given File_Name_1.txt in A1 this would spit out:
ren File_Name_1.txt “File Name 1.txt”
Of course, there may be better ways if that’s the only renaming operation you want to do.
I’ve tried a few of the annoying GUI-based programs (ever notice how many bulky Windows programs have one-liner equivalents in Unix?) but I came up with this method up on my own after taking a couple Excel-based classes and I’ve been using it for a few years now. I find it much more flexible than a special program or custom script because I can generate and massage filename lists by a variety of methods, such as a combination of ctrl+H (find&replace), sorting, string operations (mid, subst and trim are my favorites) and whatever else I can cook up in Excel. (The Text to Columns feature in Excel is very useful.) I have used this method to systematically rename processed geophysical files, relabel mp3s that have annoying extra characters or erroneous names, reconfigure memo file naming conventions at work, etc. (I could write myself scripts to, say, grab data from freedb, or strip the junk out of an Amazon album list, but I find myself using enough different list formats and using any given format infrequently enough that having one method for everything is more convenient.)
note that if you set cmd to Quick Edit mode, it’s much easier to copy and paste, though of course piping commands (e.g. dir /b /s filenames.csv) and using batch files avoids the issue.
By the way, for ID3 tags, there’s an excellent command line program from http://home.wanadoo.nl/squell/id3.html that can be used in this same manner. Unfortunately the documentation is frustratingly sparse.
Hi,
I try to follow the steps above, but I am not sure how can I apply to my scenario.
Let me give my scenario;
I have files named as
IDEA (1).txt
IDEA (10).txt
IDEA (11).txt
IDEA (12).txt
IDEA (13).txt
IDEA (14).txt
IDEA (15).txt
IDEA (16).txt
IDEA (17).txt
IDEA (18).txt
IDEA (19).txt
for example, and I want to rename them to
IDEA #001.txt
IDEA #010.txt
IDEA #011.txt
IDEA #012.txt
etc.
Can anyone help me on how to apply this trick to my case pls?
Thanks a lot in advance.
It can be done, but the ways I know of are a bit ridiculous. For instance, this beastly formula:
=”ren “”"&A1&”"” “”"&LEFT(A1,FIND(”(”,A1)-1)&”#”&TEXT(MID(LEFT(A1,FIND(”)”,A1)-1),FIND(”(”,A1)+1,3),”#000″)&MID(A1,FIND(”)”,A1)+1,LEN(A1))&”"”"
(if you just want the answer and don’t care how it works, use this and read no more. put filenames in col A, paste this into B1, drag down, copy into cmd. I hope it survives the formatting…)
Another way: sort, add a counter column and generate new file names from scratch. If A is the filename list and B is the counter column, looks like this:
=”ren “”"&A1&”"” “”IDEA #”&B1&”.txt”"”
This wouldn’t work well for your exact example since the numbers are different lengths and sorting won’t work right (though maybe you could generate a counter column that’s sorted in the same order as the data, or just manually copy and paste the files names in order). A sequential counter column would also be messed up if any file numbers in the sequence are missing.
For a more bombproof method, I’d copy the column of filenames, split into parts, use the TEXT() function to format the numbers, and recombine. It may help to read up on the TEXT, MID, LEFT, RIGHT, and FIND functions if this section doesn’t make sense.
Method 1: Split the text into several columns
1. Paste the list of filenames in column A
2. Use Notepad to make three more columns: one with everything before the open paren, one with the numbers between the parens, and one with what’s after the parens;
(a.) Paste the list of filenames to Notepad, ctrl+H to replace “(” and “)” characters with commas, save as a .csv
(b.) open in excel, and copy to the first spreadsheet.
You will get four columns: IDEA (1).txt,IDEA ,1,.txt
(You could also use Excel’s text-to-columns feature, shortcut alt+D,E; you’d need to apply it twice.)
3. In column E, use the text() function to format the file number (column C):
=”#”&text(C1,”000″)
which will give you #001 if C1 has a 1, #133 if if C1 is 133, etc.
4. In column F concatenate everything to get the updated file name:
=B1&E1&D1
which gives IDEA #001.txt
5. Put the rename formula in column G:
=”ren “”"&A1&”"” “”"&F1&”"”"
(may look weird because this site tries to use pretty quotes. oh well.)
6. Drag down all columns, copy last column into cmd.
Method 2: Use string functions to separate the filename components automatically
1. Paste the list of filenames to column A
2. (this is the least intuitive part) In column B, put a formula that will extract text from between the parens in A1:
=REPLACE(LEFT(A1,FIND(”)”,A1)-1),1,FIND(”(”,A1),”")
or since we’re already assuming the file numbers max out at 3 characters:
=MID(LEFT(A1,FIND(”)”,A4)-1),FIND(”(”,A1)+1,3)
3. Then in column C, concatenate the text before”(” , the formatted number, and the text after “)”:
=left(A1,FIND(”(”,A1)-1)&”#”&text(B1,”#000″)&mid(A1,FIND(”)”,A1)+1,len(A1))
(using len(A1) at the end ensures all the characters are grabbed; mid() doesn’t mind if it’s told to grab more characters than exist)
4. Finally, the rename equation:
=”ren “”"&A1&”"” “”"&C1&”"”"
5. Drag down all columns, copy last column into cmd.
(The top equation is just this method all jammed into one cell.)
Hi there, similar to Karin, I am trying to organise my mp3 collection. How do I rename, e.g., “01 - La Fille De Pekin (Frederick Rousseau)” to “CD1 - 01 - La Fille De Pekin (Frederick Rousseau)”.
I have thousands of similar files to rename! Help!?
this is a really easy example. pay attention ;)
=”ren “”"&A1&”"” “”"&B1&” - “&A1&”"”"
where A1 is the filename and B1 is “CD1″ or whatever CD number. Incidentally, if you want to just type
By the way, it’s easy to a VBA rename function. (you can make a personal.xls file to store persistent macros; for details see http://personal-computer-tutor.com/personalxls.htm)
Press Alt+F11, which will bring up the VBA editor. Choose Insert Module, then type this in the window that appears:
Function ren(String1 As String, String2 As String)
ren = “ren “”" & String1 & “”" “”" & String2 & “”"”
End Function
(this site reformats the ” marks so make sure that you replace them with normal quotes, probably easiest to retype manually)
this makes a function ren() that takes two cell references that are filenames and generates a line that you can copy to cmd. thus if you get the function working you could put the filename in A1, =”CD1 - “&A1 in B1, and =ren(A1,B1) in C1, drag down as appropriate.
of course, someone could make a much fancier macro that actually interacts with the system and does the renaming automatically, but that seems a bit dangerous.
Hi i have many audio files named with numbers then artist names for example SC3002-01 - Prince - Betcha By Golly Wow and SC3002-02 - D.C. Talk - Between You And Me. I want to rename the file to just the song name and put the songs in a folder named the artists name. How can i do this?
Thanks
Greg
#20 Brian, metamorphose-2 will allow you to export a snapshot of files as a text file. You can then edit the file with a text editor like notepad++ and import it into metamorphose for renaming.