Excel VBA Find and Replace: Methods and Examples

To find and replace text with Excel VBA, use the Range.Replace method and specify what to search for, what to insert, and whether the match must be a whole cell or can be part of one. For example, a macro can replace every exact “North” entry in a chosen range with “South”; use Range.Find instead when you need to inspect matches or make decisions before changing cells.

Run a basic VBA replacement

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and put a macro in the module. The example below searches a specified range on a worksheet named Data. It replaces cells whose entire contents equal North, without distinguishing uppercase from lowercase.

Save a copy of the workbook before running a macro: VBA changes cannot be undone with Ctrl+Z. The workbook must be saved in a macro-enabled format such as .xlsm if you want to keep its VBA code.

Sub ReplaceExactText()
    Dim ws As Worksheet
    Dim target As Range

    ' Work only in the named sheet and specified range.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set target = ws.Range("A2:D200")

    ' Replace whole-cell matches, ignoring letter case.
    target.Replace What:="North", Replacement:="South", _
        LookAt:=xlWhole, SearchOrder:=xlByRows, _
        MatchCase:=False, MatchByte:=False
End Sub

Change the sheet name, range, search text, and replacement text to suit the workbook. The worksheet name must match the tab name exactly. This code does not show a confirmation dialog, so check the selected range before running it. If you want to use Excel’s interface instead, press Ctrl+H on Windows or choose Home > Find & Select > Replace.

What Range.Replace does

Range.Replace searches the range on which you call it and replaces matching text. You can call it on a single column, a block of cells, or a broader range such as ws.Cells. A broader range is convenient, but it also increases the chance of changing something unintended, so a defined range is usually safer.

The key arguments are What and Replacement. LookAt:=xlWhole means the entire cell content must match; LookAt:=xlPart allows a match within the cell. MatchCase controls whether uppercase and lowercase letters must match exactly. SearchOrder sets whether Excel searches by rows or columns; it does not limit which cells are included.

Excel remembers some Find and Replace settings between operations. Microsoft documents that settings such as LookAt, SearchOrder, MatchCase, and MatchByte can persist when they are omitted. For predictable macros, specify the relevant arguments rather than relying on the last choices made in the Find dialog or by another macro.

Choose whole-cell or partial matching

The choice between xlWhole and xlPart is one of the most important safeguards. If cells contain North, Northern, and North Depot, an exact whole-cell replacement changes only North. A partial replacement also changes the longer entries wherever the text North occurs. That may be useful when standardizing a repeated phrase, but it can corrupt words or labels that merely contain the same characters.

For example, suppose column A contains the codes “AB-10”, “AB-100”, and “XAB-10”. Replacing AB-10 with CD-20 using xlPart may change all three cells, including the longer codes. With xlWhole, only a cell whose complete contents are AB-10 matches. If you need rules such as “replace this phrase only at the beginning of a cell,” test the data carefully or use a controlled loop that checks each value before editing; xlPart is not a word-boundary option.

Find matches before changing them

Use Range.Find when a simple bulk replacement is too broad. It returns a Range object for a match, or Nothing if there is no match. You can inspect the cell, its row, or related data before deciding what to do. This example reports every cell in a range whose value contains the text INV-; it does not alter the workbook.

Sub ListInvoiceMatches()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim found As Range
    Dim firstAddress As String

    ' Search the invoice-code column only.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A500")

    ' Set Find options explicitly for repeatable results.
    Set found = searchRange.Find(What:="INV-", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, MatchByte:=False, SearchFormat:=False)

    If Not found Is Nothing Then
        firstAddress = found.Address
        Do
            Debug.Print found.Address, found.Value
            Set found = searchRange.FindNext(found)
            If found Is Nothing Then Exit Do
        Loop While found.Address <> firstAddress
    End If
End Sub

Open the Immediate window in the Visual Basic Editor to see the addresses and values printed by Debug.Print. The saved first address stops the loop when the search cycles back to its starting match. This matters because FindNext wraps around to the beginning of the search range. When adapting this pattern to edit cells, test it on a copy and make sure the loop has a clear stopping condition.

Limit the search to the right cells

Define the range as specifically as possible. If the intended field is in column C, searching the entire worksheet could alter a note, a heading, or a formula elsewhere that happens to contain the same text. A table’s data body, a named range, or a carefully selected block is often a better target than every cell on the sheet.

You can also use a worksheet variable to make the target clear and avoid relying on whichever sheet happens to be active. For example, ThisWorkbook.Worksheets("Data").Range("C2:C500") refers to that range on the Data sheet in the workbook containing the code. By contrast, ActiveSheet.Range("C2:C500") depends on the user’s current selection context. That can be appropriate for a macro intended to work on the active sheet, but it is less predictable in an automated task.

Consider whether blank cells, hidden rows, or filtered-out records should be included. A normal range-wide Replace call is not a user-facing review process: it does not ask you to approve each individual match. If only visible rows should be changed, do not assume that a broad call automatically expresses that requirement; design and test a method that explicitly targets the intended cells.

Text, formulas, and special characters

VBA replacement is not limited to ordinary words. It can be useful for cleaning separators, updating labels, or correcting a repeated code. But search strings may have special meaning in Excel’s Find and Replace system: the asterisk represents any sequence of characters, and the question mark represents a single character. A tilde is used to escape wildcard characters when you need to search for a literal asterisk or question mark. Test wildcard searches on a small range first, because a pattern can match more cells than an ordinary text search.

Be particularly careful with formula cells. A change to text that forms part of a formula can change the formula itself, not just its displayed result. Before using Replace on a range that contains formulas, inspect representative cells and verify the formulas afterward. If your goal is to change calculated results, changing the source values or formula logic may be more appropriate than replacing text across a mixed range.

Codes that look numeric also need care. A cell containing the number 12 with a number format that displays 0012 is not necessarily the same as a text cell containing “0012”. Replacing text may not produce the intended result in both cases. If leading zeros are significant, store identifiers as text and check the actual cell values and formats before making a bulk edit.

Common problems and how to avoid them

Unexpected partial changes: Set LookAt:=xlWhole for exact cell matches, or inspect the consequences of xlPart on longer values before using it.
Different results on a second run: Specify the search options in the code. Excel can retain settings from previous Find or Replace operations.
No matches found: Check the worksheet name, target range, spelling, spaces, and whether the cells contain values or formulas. A trailing space can make two strings look alike while not matching exactly.
Changes appear outside the expected area: Confirm the range used by the macro and whether it refers to the intended workbook and sheet. Avoid broad targets until the code has been checked.
Macro does not run: Confirm the file is saved in a macro-enabled format and that macros are permitted by your organization’s security settings. Do not enable macros in an untrusted workbook just to run its code.

For diagnosis, temporarily use a small test range with a few known examples, including one exact match and one similar but nonmatching value. Compare the cells before and after the operation. This makes it easier to distinguish a range-selection problem from a matching-rule problem.

VBA replacement compared with Excel’s Replace dialog

The Replace dialog is usually the simplest choice for a one-time edit: it makes the search scope and options visible, and Replace All can show how many replacements were made. Use VBA when the same operation must be repeated, when the target range is determined by other code, or when replacement is one step in a larger workflow.

VBA also allows checks and decisions that the dialog cannot express as a simple text substitution. For example, a macro can find a code, inspect another column, and then update only rows that meet a condition. That extra control brings extra responsibility: code can affect many cells quickly, and macro changes cannot be reversed with Ctrl+Z. For an isolated task, the dialog may be both faster and easier to review.

On Windows, Alt+F11 opens the Visual Basic Editor and Ctrl+H opens Find and Replace in Excel. On Mac, use the Excel ribbon’s Home > Find & Select > Replace command if the shortcut differs on your keyboard or in your Excel build. Excel for the web can use its Find and Replace interface, but it cannot create, run, or edit VBA macros; open the workbook in the desktop Excel app to work with VBA.

Frequently Asked Questions

How do I replace text in an entire worksheet with VBA?
Call Replace on the worksheet’s cells, such as ws.Cells.Replace, and set the matching options explicitly. Because this searches a very broad area, use it only when every cell is genuinely in scope; otherwise specify a smaller range.

How do I replace only an exact cell match?
Use LookAt:=xlWhole in the Replace call. This matches the entire cell content rather than a substring inside a longer entry. Set MatchCase:=True as well if capitalization must be identical.

Can VBA replace text in formulas?
A replacement applied to a range that includes formulas can affect formula text. Keep the target range limited and inspect formulas before and after the change. If you only want to change displayed results, first identify how those results are produced instead of assuming a text replacement is harmless.

Why does my macro replace more cells than expected?
The range may be too broad, or the call may use partial matching. Check the target range and choose xlWhole when the full cell must match. Also check for wildcard characters and previously retained search options.

Can I undo a VBA Find and Replace?
Excel’s Ctrl+Z undo history does not undo changes made by a VBA macro. Save a separate copy before running a replacement, or build a review and recovery process into the macro if the operation is important.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.