How to Record a Macro for Find and Replace in Excel

Do you frequently replace the same text in a column, range, or entire workbook?

Manual Find and replace is a time-consuming process. Instead, you can record a macro to automate the process.

This is useful for replacing a string in a cell, multiple values, or even text in formulas across large datasets.

Before we begin, if you’re new to Find and Replace in Excel, check out this replace word in excel guide to understand its basic features.

Why Use a Macro for Find and Replace?

Here is a list of benefits of using a macro to perform find and replace operations in MS Excel.

• Eliminates repetitive work – No need to replace text manually.
• Works in a range, column, or entire workbook – Choose the scope easily.
• Finds and replaces multiple values at once – Automate bulk changes.
• Ensures accuracy – Prevents human errors in data.
• Saves time – Runs in seconds, even for large datasets.

Example Scenario: Find and Replace Using VBA

Imagine you have a price list where an old supplier name appears in multiple locations (in a column, range, and even formulas).

Before Find and Replace:

Here, the Notes column has a formula. We want to replace text in the formula as well.

The task to perform:

Find and Replace “TechCorp” with “NextGen Supplies” across all occurrences using VBA Macro (including in string values, formulas, and notes).

Step-by-Step Guide to Recording a Macro

Step 1: Enable the Developer Tab

If the Developer tab isn’t visible:

• Click File > Options.
• Go to Customize Ribbon.
• Check the box for Developer and click OK.

Step 2: Start Recording the Macro

Click on the Developer tab and select Record Macro.

Now you are inside the Record Macro window.

In the Record Macro dialog box:

• Macro name: ReplaceSupplier
• Shortcut key (optional): Ctrl + Shift + R
• Store macro in:
  • This Workbook (for the current file)
  • Personal Macro Workbook (for all Excel files)

• Click OK to start recording.

Step 3: Perform the Find and Replace Action Using VBA Macro

• Press Ctrl + H to open the Find and Replace dialog box.
• In the Find what field, type: TechCorp.
• In the Replace with field, type: NextGen Supplies.
• Choose the scope of replacement:
  • In a column → Select the column first, then run Find and Replace.
  • In a range → Select a specific range before replacing.
  • Entire workbook → Click Options > Within > Workbook.
  • Text in formula → Check Look in: Formulas.
• Click Replace All.
• A message will show the number of replacements made. Click OK.
• Click Close.

Step 4: Stop Recording the Macro

Since find and replace macro is recorded, You can stop recording by:

• Go back to the Developer tab.
• Click Stop Recording.

Step 6: Running the Macro

Now, whenever you need to replace “TechCorp” with “NextGen Supplies,” simply:

• Press Ctrl + Shift + R (if you set a shortcut).
• Or, go to Developer > Macros, select ReplaceSupplier, and click Run.

After Find and Replace:

Here, you can see that we replaced multiple values (text string) in each cell of the supplier and Notes (Formula) columns.

Tip: If your column contains “Product – Supplier,” and you only want to replace the supplier’s name, first split the text into separate columns.

Step 6: Viewing and Editing the Macro Code

To see or modify the recorded macro:

• Go to Developer > Macros, select ReplaceSupplier, and click Edit.

The VBA Editor will open with below give code:

Sub ReplaceSupplier()
    Cells.Replace What:="TechCorp", Replacement:="NextGen Supplies", LookAt:=xlPart
End Sub

Also, you can modify the macro to replace multiple values at once:

Sub ReplaceMultipleValues()
    Dim replacements As Variant
    Dim i As Integer
    
    replacements = Array("TechCorp", "OldBrand", "XYZ Ltd") ' Old values
    newValues = Array("NextGen Supplies", "NewBrand", "ABC Ltd") ' New values
    
    For i = LBound(replacements) To UBound(replacements)
        Cells.Replace What:=replacements(i), Replacement:=newValues(i), LookAt:=xlPart
    Next i
End Sub

This script will replace multiple values (Specified in an array) across the entire workbook.

Close the editor after making changes.

Bonus: Assign the Macro to a Button

For quick access, you can add a button to run the macro:

• Go to Developer > Insert and choose Button (Form Control).
• Click anywhere on the sheet.
• In the Assign Macro window, select ReplaceSupplier or ReplaceMultipleValues, and click OK.
• Rename the button (e.g., Update Supplier Name).

Now, clicking the button will instantly replace text in string values, columns, ranges, and even formulas!

Comparison Table: Find and Replace Methods

Pro Tips for Efficient Find and Replace in Excel

• Use Wildcards:
  • You can use wildcard * to replace multiple characters (e.g., Tech* finds TechCorp, TechWorld).
  • If you want to replace a single character, you can use ? wildcard (e.g., C?rp finds Corp, Carp).
• Case-Sensitive Replacement:
  • Enable the Match case in the Find and Replace dialog if you want precise replacements.
• Replacing Special Characters:
  • Use Ctrl + J in Find box to replace line breaks.
  • Use ~ before * or ? to replace them as actual characters.

Recording a Macro Find and Replace FAQs

Recording a macro for Find and Replace in Excel is a simple yet powerful way to automate repetitive tasks. Whether you’re replacing a string in a cell, multiple values, text in formulas, or working across an entire workbook, this technique can save you hours of work.

Download Practice Workbook

Practice what you’ve learned! Download the Excel Practice Sheet. Try recording a macro to automate find-and-replace tasks.

Did you find this guide useful? Let me know in the comments!