Excel VBA Find and Replace Exact Text in a Column

To replace an exact cell value in one Excel column, use VBA’s Range.Replace method with LookAt:=xlWhole. For example, restricting the range to column C and searching for Pending replaces cells whose entire contents match, rather than cells that merely contain that word.

What “exact” means in Excel

An exact match means the cell’s complete contents match the search text. If a cell contains Pending review, a whole-cell search for Pending leaves it unchanged. A cell containing only Pending is eligible for replacement. In VBA, the key setting for this distinction is LookAt:=xlWhole; xlPart searches for text within a longer cell value.

Whole-cell matching and case sensitivity are separate choices. Setting MatchCase:=False treats pending and Pending as matches; set it to True if capitalization must match exactly. Spaces count as characters, too: Pending with a trailing space is not identical to Pending. If you suspect hidden spaces, inspect or clean the data before replacing it.

VBA code to replace an exact value in a column

This macro searches column C from row 2 down to its last nonempty cell, leaving a presumed header in row 1. Change the worksheet name, column, search text, and replacement text to suit your workbook.

Sub ReplaceExactInColumnC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim target As Range

    ' Use the worksheet that contains the data.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    ' Start below the header; do nothing if there are no data rows.
    If lastRow < 2 Then Exit Sub
    Set target = ws.Range("C2:C" & lastRow)

    ' Replace whole-cell matches, regardless of letter case.
    target.Replace What:="Pending", Replacement:="Complete", _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
End Sub

The code uses explicit search and format options so it does not rely on settings left over from Excel’s Find and Replace dialog. SearchOrder:=xlByRows specifies the direction Excel uses to search the selected range; it does not broaden the range beyond column C. The replacement applies to the range provided, not to every column in the worksheet.

Save a copy of the workbook before running the macro. VBA changes cannot be undone with Ctrl+Z, so a backup is the reliable way to recover the original data if the range or replacement text is wrong.

Run the macro in Microsoft 365 for Windows

Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the macro in the file.
Press Alt+F11 to open the Visual Basic Editor. In the editor, choose Insert > Module.
Paste the code into the module and edit Sheet1, the column reference, and the two text strings. The worksheet name must match the tab name.
Return to Excel, select the macro, and run it from Developer > Macros, or press Alt+F8, select ReplaceExactInColumnC, and choose Run.

If the Developer tab is not visible, enable it through File > Options > Customize Ribbon and select Developer. Only enable macros in files you trust. On Excel for Mac, VBA is available in the desktop application; the Visual Basic Editor shortcut is commonly Option+F11 (some keyboards also require Fn). Menu names and shortcut behavior can vary with Mac keyboard settings. Excel for the web can open a macro-enabled workbook, but it cannot create, edit, or run VBA macros; use desktop Excel instead.

Why use a column range instead of the whole sheet?

Limiting the target to a specific column reduces the chance of changing unrelated values, such as notes, IDs, or formulas elsewhere in the workbook. The sample also starts at row 2 to avoid replacing a matching column heading. If your data has no header, change the start row to 1 and adjust the empty-range check accordingly.

The last-row calculation uses the bottom of column C and moves upward to the last nonempty cell. It can include blank cells between records without stopping early. However, if the column is completely empty, Excel reports row 1 as the result, and the macro exits without constructing an invalid data range. If your records may extend beyond the last populated cell in C because other columns hold data, use a known range or calculate the last row from the column that reliably defines the record boundaries.

When a cell-by-cell comparison is safer

Range.Replace is concise and useful for ordinary find-and-replace work, but Excel’s search treats asterisks and question marks as wildcard characters. If the literal text you are searching for contains * or ?, those characters can act as patterns rather than ordinary punctuation. Excel uses a tilde to escape wildcard characters in its Find interface: for example, ~* searches for a literal asterisk. A cell-by-cell comparison avoids wildcard interpretation altogether.

The following alternative changes only nonformula cells that contain text exactly equal to the search string. It compares without regard to case, matching the setting in the first macro. Use vbBinaryCompare instead of vbTextCompare for a case-sensitive comparison.

Sub ReplaceLiteralTextInColumnC()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim c As Range
    Dim oldText As String
    Dim newText As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    oldText = "Pending"
    newText = "Complete"
    lastRow = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row

    If lastRow < 2 Then Exit Sub

    ' Compare complete text values; leave formulas and other data types alone.
    For Each c In ws.Range("C2:C" & lastRow).Cells
        If Not c.HasFormula Then
            If VarType(c.Value2) = vbString Then
                If StrComp(CStr(c.Value2), oldText, vbTextCompare) = 0 Then
                    c.Value2 = newText
                End If
            End If
        End If
    Next c
End Sub

This version deliberately handles text only. It does not replace a numeric value such as the number 12, a date stored as a number, an error value, or text displayed by a formula. That narrower behavior can be valuable in a mixed column, but it is not interchangeable with a general worksheet replacement. Choose the method that matches the type of data you intend to change.

Formulas, numbers, and formatting considerations

Before running a replacement, decide whether the target column contains formulas. A broad replacement can interact with formula contents, depending on what Excel finds and how the range is used. If formulas must remain untouched, use the cell-by-cell version above, which explicitly skips cells where HasFormula is true. If you do want to change a formula, make a copy and test the operation on a small range first; changing formula text can alter the calculation, not just its displayed result.

Numbers and text that looks like a number are different cell values. A cell containing numeric 12 is not necessarily the same as a cell containing the text 12. The cell-by-cell example checks for text only, while Range.Replace may have broader effects according to the values and formulas in the selected cells. For numeric substitutions, use a deliberately designed comparison that checks the underlying value and data type rather than converting everything to text.

The sample turns off search and replacement formatting through SearchFormat:=False and ReplaceFormat:=False. This helps avoid an unexpected format criterion carried from an earlier Find operation. Replacing a value normally does not mean you are asking VBA to redesign the cell’s formatting; keep formatting changes as a separate, explicit step.

Using Excel’s Find and Replace dialog

For a one-time change, VBA may be unnecessary. Select the relevant cells in column C, then use Home > Find & Select > Replace on Windows, or press Ctrl+H. In the dialog, choose Options if needed and set Within to Sheet, Look in to Values where appropriate, and Match entire cell contents to get whole-cell matching. Enter the old and new text, then choose Replace All. Check the reported replacement count and review the affected cells.

On a Mac, use Edit > Find > Replace or the available Find and Replace command in your Excel version; the exact menu presentation may differ. Excel for the web also provides find-and-replace controls, but it does not run VBA. For repeatable, narrowly scoped changes, a macro can make the target range and options explicit. For a single small edit, the dialog is often simpler.

Common mistakes and how to prevent them

Using xlPart by accident: This can change longer entries that merely contain the search text. Use xlWhole for complete-cell matching.
Searching the entire worksheet: A worksheet-wide range can affect unrelated columns. Define a specific range, and exclude the header if necessary.
Assuming exact also means case-sensitive: It does not. Set MatchCase:=True when capitalization matters.
Overlooking spaces: Leading and trailing spaces prevent a whole-cell match. Check representative cells or clean the data first.
Replacing formula text unintentionally: Use a formula-skipping comparison or test on a copy if formulas are present.
Forgetting wildcard behavior: A literal asterisk or question mark needs special handling in a Find-style search; direct string comparison avoids that issue.
Expecting Ctrl+Z to reverse VBA: It will not undo macro changes. Save a backup before running the procedure.

Frequently Asked Questions

How do I make VBA replace only a complete cell match?
Call Range.Replace on the column range and set LookAt:=xlWhole. That setting distinguishes a whole-cell match from a substring match.

Does xlWhole make the search case-sensitive?
No. Set MatchCase:=True for case-sensitive matching or MatchCase:=False to ignore capitalization.

Can I replace text in one column without changing formulas?
Yes. Iterate through the target cells, check HasFormula, and compare only cells where it is false. The literal-text macro above uses that approach.

Will this replace text inside a longer cell value?
Not with LookAt:=xlWhole. For example, searching for Pending will not match Pending review. Use xlPart only when substring replacement is intended.

Can I use the macro in Excel for the web?
No. Excel for the web can work with macro-enabled files but does not run VBA. Open the workbook in desktop Excel for Windows or Mac to use the macro.

Can I undo a VBA replacement with Ctrl+Z?
No. VBA actions generally clear Excel’s undo history. Save a separate copy before running the macro, then verify the target range and replacement count.

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.