Excel SUBSTITUTE in VBA: Replace or WorksheetFunction

To substitute text in Excel VBA, use VBA’s built-in Replace function; it replaces every matching occurrence by default. To replace only a particular occurrence, such as the second match, call Excel’s SUBSTITUTE function through Application.WorksheetFunction.Substitute.

What Excel SUBSTITUTE Means in VBA

Excel has a worksheet function named SUBSTITUTE, and VBA has a separate built-in function named Replace. They solve similar problems—changing specified text inside a string—but their arguments work differently. This distinction matters when converting a worksheet formula into a macro or choosing how to edit text in code.

The worksheet function takes the original text, the text to find, the replacement text, and optionally an occurrence number. Without an occurrence number, it changes all matching occurrences. With one, it changes only that numbered occurrence. VBA’s Replace also changes all matches by default, but its optional arguments control the starting character position, the number of replacements, and the comparison method. It does not have a direct “replace the nth match” argument.

Neither function edits a cell on its own. Each returns a modified text value. To change a cell from VBA, your code must assign that returned value to the cell; to display the result in a worksheet formula, enter the worksheet function in a cell.

VBA Replace Syntax and Its Arguments

The VBA syntax is Replace(expression, find, replace, [start], [count], [compare]). The first three arguments are the original string, the text to look for, and the text to put in its place. The optional arguments let you fine-tune the operation:

start sets the character position at which the search begins. If you supply it, the returned string begins at that position rather than including the preceding characters. For a normal whole-string replacement, use 1 or omit it.
count limits how many matches are replaced. The default is -1, meaning replace all matching occurrences.
compare determines how text is compared. Use vbBinaryCompare for case-sensitive matching or vbTextCompare for case-insensitive matching.

For clarity, specify the comparison mode when case matters instead of depending on a module’s Option Compare setting. A binary comparison treats “North” and “north” as different strings; a text comparison treats them as equal for matching purposes. In either case, the function returns a string and does not alter the original variable unless you assign the result back to it.

A Basic VBA Replacement Example

Here is a small macro that replaces every occurrence of “colour” with “color” in a string and prints the result in the Immediate window. In the VBA editor, open the Immediate window with Ctrl+G on Windows; on Mac, use the corresponding command in the VBA editor’s View menu if that shortcut differs in your setup.

Before running any macro that changes a workbook, save a copy first. Excel macros cannot be undone with Ctrl+Z.

Sub ShowReplacement()
    Dim sourceText As String
    Dim resultText As String

    ' Replace all matches, ignoring case.
    sourceText = "The colour is a warm colour."
    resultText = Replace(sourceText, "colour", "color", 1, -1, vbTextCompare)
    Debug.Print resultText
End Sub

The printed result is The color is a warm color. Both occurrences are changed because the count argument is -1. The source variable remains unchanged; the replacement result is held in resultText. To keep the original text when matching case, change vbTextCompare to vbBinaryCompare.

Using Worksheet SUBSTITUTE from VBA

When you need Excel’s occurrence-number behavior, call SUBSTITUTE through the Excel application object. This example asks Excel to change only the second occurrence of “red” in a string:

Before running the macro, save a copy of the workbook if you are adapting it to make changes in your file; macro actions cannot be reversed with Ctrl+Z.

Sub ReplaceSecondOccurrence()
    Dim sourceText As String
    Dim resultText As String

    ' Change only the second matching occurrence.
    sourceText = "red, blue, red"
    resultText = Application.WorksheetFunction.Substitute(sourceText, "red", "green", 2)
    Debug.Print resultText
End Sub

The result is red, blue, green: the first “red” stays as it is, while the second becomes “green.” Leave off the final number to replace every matching occurrence. Worksheet SUBSTITUTE matching is case-sensitive, so “Red” is not the same match as “red.” If the input might produce an Excel error, account for that possibility in your VBA error handling; a worksheet-function call through WorksheetFunction can raise a VBA run-time error when Excel returns an error value.

Replacing Text in a Range of Cells

To apply a replacement to many cells, loop through a specific range and assign the result back to each cell. This example checks cells A2:A100, processes text constants only, and leaves formulas, numbers, and error values alone. It is deliberately limited to that range; change the range only after checking which cells your workbook should update.

Save a separate copy of the workbook before running or adapting this macro. The operation changes cell contents and Ctrl+Z will not undo a macro.

Sub UpdateTextInRange()
    Dim target As Range
    Dim cell As Range

    ' Work only with text constants in the stated range.
    Set target = ThisWorkbook.Worksheets("Sheet1").Range("A2:A100")

    For Each cell In target.Cells
        If Not cell.HasFormula Then
            If Not IsError(cell.Value2) Then
                If VarType(cell.Value2) = vbString Then
                    cell.Value2 = Replace(CStr(cell.Value2), "Ltd", "Limited", 1, -1, vbTextCompare)
                End If
            End If
        End If
    Next cell
End Sub

Replace Sheet1 with the actual worksheet name, including its exact spelling. This code edits the cell values in place, so keep the original data elsewhere if you may need it. Checking HasFormula prevents the macro from replacing a formula with its displayed text. If the goal is to update formulas too, decide how to handle formula text separately rather than removing that safeguard without considering the consequences.

How to Replace Only One Occurrence

There are two common ways to target a particular occurrence. If you specifically want the worksheet function’s “nth match” behavior, use Application.WorksheetFunction.Substitute as shown above. It is concise and its final argument identifies the occurrence number, starting at 1. For example, 2 means the second match, not the second character.

For a replacement limited by position or number of changes rather than occurrence number, VBA’s Replace may be enough. Its count argument limits how many matches are changed from the search start onward. However, that is not a general substitute for choosing an arbitrary nth match: a count of 1 changes the first match found, not necessarily the match number you intended. Avoid passing a non-1 start unless you want the returned string to omit the characters before that position.

If you need a custom rule—such as replacing the third occurrence only when it follows a particular word—find and validate the relevant position first, then build the output deliberately. For straightforward “replace the nth matching text” tasks, the worksheet function is usually easier to understand and maintain.

Choosing Between Replace, SUBSTITUTE, and Find and Replace

Choose the method based on where the work happens and whether it needs to repeat:

Use VBA Replace for strings being processed in code, especially when you want to control case sensitivity or limit the number of replacements.
Use Application.WorksheetFunction.Substitute when VBA needs Excel’s occurrence-number option.
Use the worksheet SUBSTITUTE function when you want a visible, recalculating result in a cell rather than a macro that writes values.
Use Excel’s Find and Replace interface for a one-time manual change. On Windows, open it with Ctrl+H; on Mac, use Find and Replace from Excel’s Edit menu or the applicable Home ribbon command.

In Microsoft 365 for Windows, the Home ribbon’s Find & Select menu provides Find and Replace. Excel for Mac has its own ribbon and menu layout, so the exact route can differ. Excel for the web supports many worksheet functions but does not run VBA macros; use desktop Excel to run VBA. A formula is often preferable when the source data should remain intact and the changed text should update automatically. A macro is more useful when a repeatable process must write results into a defined set of cells.

Common Problems and Misconceptions

A frequent mistake is assuming VBA’s Replace has the same fourth argument as worksheet SUBSTITUTE. It does not: VBA’s fourth argument is start, while the worksheet function’s fourth argument is the occurrence number. Passing 2 as the fourth argument to VBA Replace starts searching at character two; it does not select the second match.

Another source of surprises is case sensitivity. VBA comparison depends on the chosen comparison mode or module setting, while Excel’s worksheet SUBSTITUTE is case-sensitive. Also, neither function treats asterisks or question marks as wildcards in the way Find and Replace can when its wildcard options are enabled. These functions look for the specified text as text.

Finally, assigning a replacement result to a cell can overwrite the value or formula already there. A macro that loops through a range should specify its target sheet and cells, check for formulas and errors where appropriate, and be tested on a copy. Replacing text in a workbook can also affect identifiers, codes, or parts of longer words, so choose a distinctive search string and inspect the result before using the change broadly.

Frequently Asked Questions

What is the VBA equivalent of Excel SUBSTITUTE?
VBA’s built-in Replace function is the usual equivalent for replacing text in a string. Use Application.WorksheetFunction.Substitute when you need the worksheet function’s optional occurrence-number argument.

Does VBA Replace change every occurrence?
Yes, by default it replaces all matches. You can limit the number of changes with the count argument. Use vbBinaryCompare or vbTextCompare to make the comparison mode explicit.

How do I replace the second occurrence in VBA?
Call Application.WorksheetFunction.Substitute(text, oldText, newText, 2). The last argument selects the second match. This Excel function is case-sensitive.

Does Replace change the cell automatically?
No. It returns a modified string. Assign the result to a cell, such as a particular range value, if the workbook needs to change. Check whether that assignment would overwrite a formula or existing data.

Can I run Excel VBA in Excel for the web?
No. Excel for the web can open workbooks that contain macros, but it cannot run VBA. Open the workbook in the desktop Excel application to execute a macro.

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