How to Rename Files in a Folder in Excel (2 Easy Ways)

Here, we will need some files instead of a data set to perform my task. We will rename the following files which all are located in the same folder.

Method 1 – Utilize CMD Prompt to Rename Files in a Folder

Steps:

Type cmd in the address bar of the same folder where the files are for renaming.
Press Enter to open the command window with the address pointer in the folder.

Type ren in the command and put a space.
Type one of the file names from the folder in the double quotation mark and a space.
Type the new name of the file that you want to give in the double quotation and press Enter.

This will change the selected file name.

To determine the files in the folder, type dir /b in the command prompt and press Enter.

You will see all the file names of that folder in the output stream.

Select all the file names and press Ctrl + C to copy them.

Open an Excel sheet and paste the name of the files using Ctrl + V like the following image.

Select the cell range B5:B12 and press Ctrl +C.

Paste them in column C under the Extensions header.

To separate the extensions from the file names select cell range C5:C12 and press Ctrl + H.

You will see the Find and Replace dialog box.
In the Find what box, type *..
In the Replace with box, type ..
Press Replace All.

This will keep only the extensions in column C.

In column D under the New File Names header, type your desired file names.

Type the following formula in cell E5 to merge the new file names with their respective extensions.

=D5&E5

Press Enter and use AutoFill to see the desired file names with extensions for the whole column.

Insert double quotations manually in cell range F5:F12.

To add the quotation sign before and after the new file name, insert the following formula in cell G5:

=F5&E5&F5

Hit Enter and drag the Fill Handle to get the desired results for all the file names.

To add a quotation sign into your old file names, type the following formula in cell B15:

=F5&B5&F5

Press Enter to see the desired result, and drag the formula to the lower cells of the column with AutoFill.

Type ren in cell range D15:D22.

To match the renaming formula like the cmd prompt, we need to add spaces. For that, select cell E15 and press the space bar once on your keyboard. (Alternatively, you can skip this and use ” “ (space in quotes) instead of the cell references for E15:E22.)

Press Enter and repeat the same process for the lower cells.

Use the following formula in cell F15 to prepare the syntax.

=D15&E15&B15&E15&G5

Press Enter and then use the Fill Handle for the lower cells of the column.

Select cell range F15:F22 and press Ctrl + C to copy the cell values.

Go into the command prompt and paste the data that you copied in the previous step.

Press Enter to run the commands.
Go back into the folder where your files are, and you will find each of your file renamed after performing the previous step.

Method 2 – Apply VBA Code to Rename Multiple Files in a Folder

Steps:

Our goal is to rename all these files using VBA code.

Open your Excel worksheet, and in two separate columns, type the old names of the files and the names you want to give after renaming.
Go to the Developer tab of the ribbon, and from the Code group, choose Visual Basic.

In the VBA window, go to the Insert tab and select Module.

Copy and paste the following code into the module.

Sub Rename_Files_in_A_Folder()
Dim selected_folder As String
Dim selected_files As String
Dim nRow As Long
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show = -1 Then
selected_folder = .SelectedItems(1)
selected_files = Dir(selected_folder & Application.PathSeparator & "*")
Do Until selected_files = ""
nRow = 0
On Error Resume Next
nRow = Application.Match(selected_files, Range("B:B"), 0)
If nRow > 0 Then
Name selected_folder & Application.PathSeparator & selected_files As _
selected_folder & Application.PathSeparator & Cells(nRow, "C").Value
End If
selected_files = Dir
Loop
End If
End With
End Sub

VBA Breakdown

Name the sub-procedure.

Sub Rename_Files_in_A_Folder()

Declare the variables.

Dim selected_folder As String
Dim selected_files As String
Dim nRow As Long

Create a dialog box for choosing a single folder for multiple files.

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False

Select the files from the desired folder and match the names with the name given in column B of the worksheet.

If .Show = -1 Then
selected_folder = .SelectedItems(1)
selected_files = Dir(selected_folder & Application.PathSeparator & "*")
Do Until selected_files = ""
nRow = 0
On Error Resume Next
nRow = Application.Match(selected_files, Range("B:B"), 0)
If nRow > 0 Then

Rename the file as per the given name in column C of the worksheet and save the in the folder.

If nRow > 0 Then
Name selected_folder & Application.PathSeparator & selected_files As _
selected_folder & Application.PathSeparator & Cells(nRow, "C").Value
End If
selected_files = Dir

Save the code and press F5 or the Run button.

After running, the code will ask you to select the folder in which you have the files for renaming.
Selecting the folder and press OK.

This action will change the name of the files in your folder as per the information in the sheet.

Things to Remember

Remember to insert the proper folder directory and command in the command prompt otherwise, you will not get your desired result.
In the VBA code, insert the proper cell range of the file names for renaming the files successfully.