Excel helps to make work easier when dealing with data. You can use it to automate tasks that could otherwise consume many hours. For example, using Excel, you can rename hundreds to thousands of files in a folder. If you were to do it manually then this would take a day or two. But with Excel automation you can do it in less than 3 minutes.
Use Excel plus CMD to rename files in a folder
CMD is a powerful tool on windows that executes commands in the operating system. It’s a text based Graphical User interface that you can use to interact with the computer. This means it can execute commands such as renaming files. Here are the steps
How to rename a file using CMD
First before I show you how to rename bulk files using Excel and CMD you need to understand how to rename one file. This will help you understand the process and do for bulk files easily.
1. First open the folder containing files you wish to rename.
2. Type CMD on the folder address bar and click enter. This Opens CMD ready to execute commands for the current folder
3. Let’s say you want to rename a file named “Best corset waist trainer.docx” to “article 1.docx”
Type the following command ren “old file name” “new file name”
Old file name is the current file name, while the new file name is the name that you want to change to. Therefore the command to achieve this will be ren “Best corset waist trainer.docx” “article 1.docx”
4. Type it in the command prompt and press enter
As you can see in the image below, the file name has successfully been renamed. 
How to rename many files at once using Excel and CMD
We only need two things when renaming bulk files using Excel and CMD. That is the current file names and the new file names.
If you have hundreds of files in a folder, then manually extracting the file names can be challenging. This is where the CMD comes in.
You can list all the file names using a command. Once we have the old and new file names, we can use Excel to make work easier in constructing the command to change file names. Here are the steps:
1. Open the folder containing the files you want to rename
2. Type CMD on the folder’s address bar and click enter
This will launch CMD on the current path
3. Type the Command dir /b to list all the file names in that folder, including their extensions
As you can see, the CMD has listed all the files in that folder.
4. Copy and paste the file names into an Excel document. First, highlight the names using the mouse, then use the shortcut ctrl+C to copy. Now paste it into your Excel.
5. Extract the file extensions in the next Column. Use Find and Replace. On find SECTION TYPE *. Then on the replace with section, type a dot. Finally, click on Replace all
6. Write the new names in the next blank Column
7. In the next column, type the new names plus their extensions
8. Create a column of quotation marks because, in the command, all the names are in quotation marks
9. Create a new column containing new names inside quotation marks
10. Next, create a column containing old names and quotation marks
11. Create a column for the run command
12. Since there is a space between the two names in the CMD, we need to have a column for spaces.
13. Lastly, create a column for the command. Join the command using the & symbol.
14. Drag the formula down to the rest of the cells to create the command for all files automatically.
15. Now copy the command column and paste it to the CMD prompt
16. The names in the folder will be renamed automatically.
Download the Practice template for renaming multiple files in a folder
Use VBA Macro
You can also use VBA macro to change the names of multiple files at once in Excel. Follow these steps.
1. First, copy the list of file names from the folder to Excel (You can get a list of file names using CMD like in the method above or by using VBA code. )
To get the list of file names using VBA, you need to open the VBA editor>Insert>Module, then copy and paste the code below.
Sub LoopThroughFiles()
Dim oFSO As Object
Dim oFolder As Object
Dim oFile As Object
Dim i As Integer
Dim folderPath As String
' Prompt the user for the folder path
folderPath = InputBox("Enter the folder path:")
' Check if the user has provided a folder path
If folderPath = "" Then
MsgBox "No folder path provided. Exiting the macro."
Exit Sub
End If
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(folderPath)
For Each oFile In oFolder.Files
Cells(i + 1, 1) = oFile.Name
i = i + 1
Next oFile
MsgBox "File names have been listed successfully."
End Sub
Excel will prompt you to enter the folder in which you want to list file names.
2. Type the folder path and press ok
The list of files in the folder will be updated in the first Column of the active sheet.
4. Now place the new names of the files in the next Column and apply the following VBA code.
Be careful, as different operating Systems use different sorting algorithms. Confirm that the adjacent contains the correct name to replace the old name.
5. Copy and Paste the following code into the VBA editor
Sub RenameFiles()
Dim folderPath As String
Dim oldNames As Range
Dim newNames As Range
Dim cell As Range
Dim oldName As String
Dim newName As String
Dim fullPath As String
' Prompt the user to select the folder path
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Folder"
.Show
If .SelectedItems.Count = 0 Then
MsgBox "No folder selected. Exiting the macro."
Exit Sub
End If
folderPath = .SelectedItems(1)
End With
' Prompt the user to select the old names range
On Error Resume Next
Set oldNames = Application.InputBox("Select the range of old names", Type:=8)
On Error GoTo 0
' Prompt the user to select the new names range
On Error Resume Next
Set newNames = Application.InputBox("Select the range of new names", Type:=8)
On Error GoTo 0
' Check if the selected ranges have the same number of cells
If oldNames.Cells.Count <> newNames.Cells.Count Then
MsgBox "The selected ranges must have the same number of cells. Exiting the macro."
Exit Sub
End If
' Rename the files
For Each cell In oldNames
oldName = cell.Value
newName = newNames.Cells(cell.Row - oldNames.Cells(1).Row + 1).Value
fullPath = folderPath & Application.PathSeparator & oldName
' Check if the old file exists
If Dir(fullPath) <> "" Then
' Rename the file
Name fullPath As folderPath & Application.PathSeparator & newName
End If
Next cell
MsgBox "Files have been renamed successfully."
End Sub
6. Run the code. This will prompt you to select the folder containing the files
Select the folder and press ok.
7. Excel will prompt you to select the range containing old file names. Use the mouse to select and press ok
8. Next, it will prompt you to select the range containing the new names
Select and press ok
9. The files in the folder will be renamed successfully
An error may arise if the range of cells containing old and new names doesn’t match in numbers. Otherwise, that’s it.
Use Kutools and VBA
You can also use an add-in to list the file names on your spreadsheet. Then use the VBA code we have used above to rename the files. Here are the steps
1. Download Kutools for Excel from the official website
2. Install the program and launch the Excel Software.
3. Click on the Kutools Plus tab, then click on Import and Export Drop down and choose the filename list from the options given.
4. Click the folder icon to choose the target folder and then select the option All files
5. Lastly, click ok. The file names will be listed in the first column, while other file properties will be listed in the next columns.
The last step is to add the new names in a column and use VBA code to rename the files.
Final Thoughts
That’s how to rename multiple files in a directory using Excel. Whether it’s images, PDFs, Excel files, or documents, the methods in this article will help you. Let us know in the comments if this guide helped you.
