How to manipulate filenames in Excel and rename files with Command Prompt

Brainless repetitive work is not my thing

I have a bunch of product image files, named with their EAN-codes(barcodes). We have this big client who wants their product images named with their own article number and not with the EAN-Code. In this example, I have only 16 images but in the actual case I had 300 images that I renamed by hand at the time and I REALLY REALLY didn't want to rename every file by hand every time I have to send files to that client.

So, I knew that I had an Excel file that contains the EAN-Codes, product names, and the associated Article Number, and I sure as shit lacked the enthusiasm to do this time-consuming endeavor every time. I knew there's got to be a way to utilize the data that a had in one go, with minimal manual work. Or more like hoped there would be something that could make my life a whole lot easier.

So I embarked on a mission to find an easy way to rename multiple files in batch with Excel data.

For all of you with the attention span of a fruit fly

We will be covering the following things

After some digging around, there IS hope

Apparently there were a few different ways that I could approach this dilemma but I decided to go with the tools that I got, it turned out that all I needed was an Excel and command prompt.

The Command Prompt is used to copy filename list if needed and for running the renaming process

The Excel is used to manipulate the filenames the way we want and compiling the renaming commands for the Command Prompt

Getting Started with Command Prompt.

For starters, I will show a few tricks that I picked up when searching for the answer. These might come in handy at some point. In my case, I didn't need a copy a list of my files so I could manipulate the filenames because they were already named with the EAN-Codes that I had in my excel so it was easy to make the filenames from that information but I will show you a way to use CMD Prompt to copy a list of filenames from a specific folder.

1-1. To start the command prompt.(option 1)

I am using Windows 10 but this will work on older versions too. This is probably the way most of you are accustomed to open The Command Prompt. 1. Go to the Start menu (the Windows logo on the bottom left on your screen). 2. You can just start typing to find anything, so type "cmd" and 3. Open the "Command Prompt"

1-2. Default CMD Window

This way Command Prompt will open the default folder which is the Windows current users folder. This is good and all in many cases but if you are like me and you rarely or never use the Command Prompt you don't remember all the commands like in our case how to go to a different folder. Or change drives.

The problem is that I need to open the folder where the images are which is "D:\BLC\Example-files\169"

Change file path When you want to change path you are currently on you need to type "cd /d" before the path where you want go. So the command in my case would be
"cd /d D:\BLC\Example-files\169"

2. Start CMD in the right folder (option 2)

The easiest way to open Command Prompt and in the right folder is simply to type "cmd" in the address bar in File Explorer when you have the right folder opened. This will start Command Prompt with the right file path opened so there is no need to do any commands.

3-1. Get a list of all the files in a folder.

In my case, I don't need to do this but I will show you how if you need to and you don't have any third-party app that can do this.

First you need to get a list of all the files in this folder. That you can achieve this by typing "dir /b" in the Command Prompt to get just the file names without any other data.

3-2. Copy the list of all the files in a folder.

When you see the file list, select the files with your mouse by dragging across the files with your right mouse button pressed down. Then you can press Ctrl+C on your keyboard to copy the filenames

Let's manipulate the filenames in Excel

Next I will show you some tricks that you can do to modify the filenames before you rename the files. We will be talking about how to separate filename and the file extension, how to add and replace things to the name with Excel functions. So Let's open Microsoft Excel and get those filenames in there.

First thing first, paste the filenames to Excel

Create a new blank Excel sheet. Then select the first Cell, as in A1 and press Ctrl+v to paste the filenames in the excel file. The files should separate each on its own cell.

1. How to extract just the filename

If you want to manipulate the filenames you probably want to exclude the file extension from the name. For this, we're going to use an excel function called LEFT which, as the name says, extracts as many characters you define from left to right.

So, we want to extract everything in the left side of the dot before the jpg. To get the number of characters before the dot we have to use a function called SEARCH which finds the dot and returns the position to the LEFt function.

So the function goes "=LEFT(A1;SEARCH(".";A1;1)-1)"

We are looking in the left of the value in A1
We are searching position of a dot in A1, only 1 character long
We don't want to include the dot in the results so -1 character

1-2. Apply function to all cells

All you need to do now is to apply the function to all the filenames. You can do it by grabbing the bottom right corner of the cell where the function is and drag down.

2. How to extract just the file-extension

After you have done what you wanted to do to the poor helpless filename you probably want to add the file extension back to the filename.

So for this, we will use an excel function called RIGHT and as you might have guessed this one extracts characters from right to left.

So the function goes
"=RIGHT(A1;LEN(A1)-SEARCH(".";A1;1)+1)"

We are looking in the right of the value in A1
We need to whole lenght of A1
We want to subtract the search results from the length
We are searching for a dot in A1, only 1 character
We don't want to include the dot in the results so +1 character because we're coming from the right this time.

3. How to add Prefix to the filename

In some cases you might want to add a prefix to your filenames. That is easy with "=CONCATENATE" which is a very handy tool in many cases. You can combine data from different cells how ever you want and add your own data to the mix

So the function goes
"=CONCATENATE("New_";B1)"

First we want to add a string "New_" at the beginning, this can be anything you want
Then we want to add the value of B1 after that, you can use what ever cell you want or even multiple cells.

4. How to add Suffix to the filename

In some cases you might want to add a suffix to your filenames. We can use "=CONCATENATE" for this one too. You can combine data from different cells how ever you want and add your own data to the mix

So the function goes
"=CONCATENATE("B1;_old")"

First we want to add the value of B1,you can use what ever cell you want or even multiple cells.
Then we want to add a string "_old" after that, this can be anything you want

3. How to replace part of the filename

In some cases you might want to replace something in the name a specific character or word.. We can use "=SUBSTITUTE" for this particular operation. Just point it at something you want to replace and bam, it's replaced.

So the function goes
"=SUBSTITUTE(B1;"614";"770";1)"

What we want to replace is in cell B1
We want to replace the number 614
We want to replace it with 770
only 1 (the first) occurence of the 614

How to rename the actual files

The actual renaming process is done with Command Prompt using the filenames we just have modified. We only need to do one more Excel trick to prepare our filenames so they'll be ready to use.

Rename command
The renaming command in Command Prompt goes like this "ren the-old-filename.jpg the-new-filename.jpg"

What we need to do in Excel is to organise the data that we have to form that command.

How to make the rename command

We will be using the "=CONCATENATE" function to create the rename command for Command Prompt. In my case, all I need is to replace the EAN-code in the file name with the client's own article number. In your case, you might have used the previous examples to modify the filename to suit your need, so select the cells accordingly.

So the function goes
"=CONCATENATE("ren ";A2;B2;" ";C2;B2)"

We want to add "ren " to the beginning
We want to take the old filename from A2
We want to add the extension from B2 (if you have the file-extension in the old filename then skip this)
Add space between the old and new filenames
Take the new filename from C2
Add the extension from B2

Paste the filenames into Command Prompt

If you don't have Command Prompt open, open it as I showed you earlier. Then just Ctrl+v paste the data and let Command Prompt do the work. It should have been over very quickly and you should now have files with an all brand spanking new filenames.

So there you have it

Hope this helped you as much it did me. If you have questions feel free to write a comment below.

BLC Blog / Windows / Filesystem / How to manipulate filenames in Excel and rename files with Command Prompt