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

Get FREE Advanced Excel Exercises with Solutions!

Renaming files might not sound difficult to do for most people. But, renaming a large number of files one by one can become very tedious and time-consuming. Here, you can utilize the golden touch of Microsoft Excel to perform this task with much ease. In this article, I will show you how to rename files in a folder in Excel.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


2 Easy Ways to Rename Files in a Folder in Excel

In this article, you will see two easy ways to rename files in a folder in Excel. In the first method, I will utilize the Command Prompt to rename files in a folder. In the second method, I will apply a VBA code for the same purpose.

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

2 Easy Ways to Rename Files in A Folder in Excel


1. Utilize CMD Prompt to Rename Files in a Folder

In the first method, I will utilize the Command Prompt or CMD to rename files in a folder in Excel. Here, I will show both the procedures to rename a single file and multiple files using this method. For a better understanding, go through the following steps.

Steps:

  • First of all, type cmd in the address bar of the same folder where the files are for renaming.
  • Then, after typing press Enter.

Typing cmd in Address Bar for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

  • Secondly, to rename a single file, type ren in the command and give a space.
  • Then, type one of the file names from the folder in the double quotation mark and give a space again.
  • After that, type the new name of the file that you want to give in the double quotation and press Enter.

  • Consequently, this will change the selected file name like the following image.

  • Fourthly, to rename all the files in the folder, type dir /b in the command prompt and press Enter.

  • Consequently, you will be able to see all the file names of that folder after performing the previous step.

  • Afterward, select all the file names and then press Ctrl + C to copy them.

  • After copying, open an Excel sheet and paste the name of the files using Ctrl + V like the following image.

  • Then, again select the cell range B5:B12 and press Ctrl +C.

  • Then, paste them in column C under the Extensions header.

  • Consequently, to separate the extensions from the file names select cell range C5:C12 and press Ctrl + H on the keyboard.

  • After that, you will see the Find and Replace dialog box.
  • From the box, in the Find what type box type *..
  • Then, in the Replace with type box type ..
  • Lastly, press Replace All.

  • Consequently, this will keep only the extensions in column C.

  • Then, in column D under the New File Names header, type your desired file names.

  • Afterward, type the following formula in cell E5 to merge the new file names with their respective extensions.
=D5&E5

Using Ampersand to Write New File Name for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

  • Then, press Enter and use AutoFill to see the desired file names with extensions for the whole column.

  • After that, insert double quotations manually in cell range F5:F12 using your keyboard.

Inserting Double Quotation Sign for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

  • After that, to add the quotation sign before and after the new file name, insert the following formula in cell G5.
=F5&E5&F5

Inserting Double Quotation Sign for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

  • Consequently, hit the Enter button and drag the Fill Handle for getting the desired results for all the file names.

  • Afterward, to add a quotation sign into your old file names, in cell B15, type the following formula.
=F5&B5&F5

  • Consequently, press Enter to see the desired result, and to drag the formula to the lower cells of the column use AutoFill.

  • Then, using your keyboard type ren in cell range D15:D22.

  • To match the renaming formula, like the cmd prompt, I need to add spaces.
  • For that, select cell E15 and press on the space bar once on your keyboard.

  • Then, press Enter and repeat the same process for the lower cells.

  • In this step, I will prepare the entire syntax to enter into cmd for renaming.
  • For that, use the following formula in cell F15.
=D15&E15&B15&E15&G5

  • Again, to see the desired result press Enter and then use Fill Handle for the lower cells of the column.

  • Then, select cell range F15:F22 and press Ctrl + C on your keyboard to copy the cell values.

Writing Renaming Syntax in Worksheet for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

  • Afterward, come back into the command prompt and paste the data that you copied in the previous step.

Applying Renaming Syntax in CMD for Renaming Multiple Files for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

  • Finally, go back into the folder where your files are and you will find each of your file renamed after performing the previous step.

Showing Final Result for Utilizing CMD Propmt as An Easy Way to Rename Files in A Folder in Excel

Read More:  How to Rename a Workbook in Excel (4 Easy Methods)


2. Apply VBA Code to Rename Multiple Files in a Folder

I will apply VBA code as my second method, to rename files in a folder in Excel. You can use this Excel macro to rename multiple files in a folder. For the detailed procedure, see the following steps.

Steps:

  • First of all, look at the following image, which shows multiple files in a single folder.
  • My goal is to rename all these files using a VBA

  • Secondly, 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.
  • Then, go to the Developer tab of the ribbon and from the Code group, choose Visual Basic.

Selecting Developer Tab for Applying VBA as An Easy Way to Rename Files in A Folder in Excel

  • Thirdly, in the VBA window, go to the Insert tab, and from there select Module.

  • Fourthly, 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

Writing Code for Applying VBA as An Easy Way to Rename Files in A Folder in Excel

VBA Breakdown

  • Firstly, name the sub-procedure.
Sub Rename_Files_in_A_Folder()
  • Secondly, declare the variables.
Dim selected_folder As String
Dim selected_files As String
Dim nRow As Long
  • Thirdly, create a dialog box for choosing a single folder for multiple files.
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
  • Then, 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
  • Finally, 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
  • After that, save the code, and press F5 or the Run button, after keeping the cursor in the module to run the code.

  • Consequently, after running, the code will ask you to select the folder in which you have the files for renaming.
  • After selecting the folder press OK.

Selecting Folder after Applying VBA Code as An Easy Way to Rename Files in A Folder in Excel

  • Finally, this action will change the name of the files in your folder as per your desire.

Showing Final Result for Applying VBA Code as An Easy Way to Rename Files in A Folder in Excel

Read More: How to Rename a Sheet Without Using Mouse in Excel (2 Ways)


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.

Conclusion

That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to rename files in a folder in Excel. Please share any further queries or recommendations with us in the comments section below.

The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.


Related Articles

Md. Araf Bin Jayed

Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo