To substitute text with VBA, use the built-in Replace function for a string, or the Range.Replace method to change matching text in selected worksheet cells. For example, Replace("old text", "old", "new") returns “new text”; use a range method when you want Excel to update cells directly.
What “substitute text” means in Excel VBA
In ordinary Excel use, substituting text means finding a character sequence and replacing it with another. VBA offers two closely related approaches: a function that returns a changed string, and a method that changes cell contents in a range. The right choice depends on whether your text is already in a VBA variable or is stored in worksheet cells.
This distinction also clears up a common naming confusion. VBA’s Replace function substitutes matching text. Excel’s worksheet function named REPLACE works by character position: it removes a specified number of characters starting at a specified position, then inserts replacement text. Excel also has a worksheet function named SUBSTITUTE, which replaces matching text. Similar names do not guarantee identical behavior across VBA and worksheet formulas.
How VBA’s Replace function works
The VBA function has the general form Replace(expression, find, replacewith, [start], [count], [compare]). It takes text, looks for the requested sequence, and returns a new string. It does not automatically write that result back into a worksheet cell or change the original variable unless you assign the result.
For instance, this statement returns a version of sourceText in which every matching occurrence of “cat” has been changed to “dog”:
resultText = Replace(sourceText, "cat", "dog")
The optional arguments control where the search starts, how many matches to change, and whether matching is case-sensitive. If you omit the optional comparison argument, VBA uses the module’s Option Compare setting; when no setting is specified, the usual default is binary, case-sensitive comparison. Supplying vbTextCompare explicitly makes the comparison case-insensitive. Supplying vbBinaryCompare makes it case-sensitive.
Replace text inside a VBA string
Use the function when text is being assembled, read into a variable, or processed before it is written anywhere. This small example replaces every capitalization of “north” in a string, then prints the result in the Immediate window:
Option Explicit
Sub SubstituteInString()
Dim sourceText As String
Dim resultText As String
' Compare without regard to letter case and replace every match.
sourceText = "North, north, NORTH"
resultText = Replace(sourceText, "north", "south", 1, -1, vbTextCompare)
Debug.Print resultText
End Sub
The printed result is south, south, south. The replacement uses the exact capitalization you provide; VBA does not preserve the capitalization pattern of each matched word. The count argument is set to -1 here to request all matches. To change only a limited number, use a positive count. Assigning the result matters: a call to Replace produces a string, so leaving the result unassigned does not update sourceText.
Replace matching text in worksheet cells
For a simple bulk change, use the worksheet range’s Replace method. The example below changes partial matches in A2:A100 on a worksheet named Data. It searches without regard to case and explicitly sets the main search options so the result does not depend as much on settings left behind from Excel’s Find and Replace dialog.
Option Explicit
Sub SubstituteInCells()
Dim ws As Worksheet
' Make a copy of the workbook before running a macro.
Set ws = ThisWorkbook.Worksheets("Data")
ws.Range("A2:A100").Replace What:="colour", Replacement:="color", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False
End Sub
With LookAt:=xlPart, a cell containing “colourful” can become “colorful,” not just a cell whose entire content is “colour.” Use LookAt:=xlWhole when you want to replace only cells that match the search text as a whole. Change the worksheet name and range to fit the workbook. A range method changes cells directly; it does not return a replacement string for you to assign.
VBA code belongs in a standard module in the desktop version of Excel. On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, paste the code, and run the procedure from the editor or from Developer > Macros. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. On Mac, use the Visual Basic Editor from the Developer tab or the Tools > Macro menu; keyboard shortcuts can depend on the keyboard’s function-key settings. Excel for the web does not run VBA macros, so open the workbook in desktop Excel to use this code.
Choose the right method for the job
Use the string function when you need to transform text as part of a larger procedure. For example, a macro might read a filename, correct a label, and then write the result to a particular cell. Use Range.Replace when the task is a broad find-and-change within a known cell range. For a one-time manual correction, Excel’s Find and Replace dialog may be simpler than creating a macro.
- One string in code: use
Replace, then assign its returned value. - Many worksheet cells: use
Range.Replaceon a deliberately selected range. - One-off edits: use Home > Find & Select > Replace, or press Ctrl+H on Windows. On Mac, use Edit > Find > Replace; shortcut availability can vary by keyboard configuration.
- Calculated output in a cell: consider the worksheet
SUBSTITUTEfunction, especially if the result should update when the source text changes.
For worksheet calculations, SUBSTITUTE replaces matching text and can target a particular occurrence with its optional instance number. By contrast, the worksheet REPLACE function is positional. In VBA, the function called Replace is the text-matching substitute operation, so do not assume it behaves like Excel’s positional worksheet function.
Case, partial matches, and specific occurrences
Decide how narrowly the macro should match before applying it to a large range. A case-insensitive search for “may,” for example, can match “May” as well as “may.” That might be useful for standardizing text, but it could also change a month name unexpectedly. A case-sensitive comparison avoids that particular match. The string function’s comparison argument and the range method’s MatchCase setting control case behavior in their respective contexts.
Partial matching can also change more than a standalone word. Replacing “art” with “craft” can alter “partial” or “artist” when the operation searches within cell contents. Excel’s basic replace operation does not offer a general whole-word matching switch comparable to word-processing software. If only complete words should change, consider checking the text in VBA with more specific logic or using a carefully designed search pattern; test the approach against examples that should and should not match.
If only one occurrence should change, the VBA string function’s count argument can limit substitutions. The worksheet range method instead applies its search-and-replace behavior to the selected range; it is not a direct substitute for a controlled, per-string occurrence count. For a specialized rule, process each eligible text value separately and write the changed value back only after deciding what counts as a match.
Prevent accidental changes and troubleshoot results
A bulk replacement can affect more cells than intended, especially when the range is a whole column or the search text is short. Select the smallest practical range, use xlWhole where appropriate, and make a backup copy first. Excel macros cannot be undone with Ctrl+Z, so the normal Undo command is not a reliable recovery plan after a macro changes cells. Saving a separate copy before running code is the safest basic precaution.
- Nothing changes: confirm the worksheet name, target range, spelling, and whether the comparison is case-sensitive. Check that the target text actually occurs in the selected cells.
- Too much changes: switch from partial to whole-cell matching if that matches the intended rule, and narrow the range.
- Results differ from a previous run: set the relevant search options explicitly. Excel’s Find and Replace settings can persist between uses, so an omitted option may not behave as expected.
- A formula changes unexpectedly: avoid running a broad replacement over formula cells unless changing formula text is intentional. Test on a copy and inspect the affected formulas afterward.
- The code does not run: check that macros are permitted by your organization’s security settings, that the workbook is opened in desktop Excel, and that the referenced worksheet exists.
Also distinguish a text replacement from changing a value’s display. A cell may show formatted dates or numbers even though its underlying value is not ordinary text. Replacing text is generally suited to labels, imported descriptions, and other text content; it is not a dependable way to alter numeric values or number formats.
Alternatives and platform notes
Excel’s built-in Find and Replace is a good choice when a person needs to review and perform a change without maintaining code. A worksheet SUBSTITUTE formula is useful when you want a separate, recalculating result rather than overwriting source data. VBA is more suitable for repeatable procedures, conditional rules, or tasks that need to combine replacement with other workbook actions.
Excel for Microsoft 365 on Windows supports VBA in the desktop application. Excel for Mac also supports VBA, though some features and object-model behavior differ from Windows, so Windows-specific automation should be checked before relying on it on a Mac. Excel for the web can display a workbook containing macros, but it cannot create or run VBA; use the desktop app or consider Office Scripts for browser-based automation. Office Scripts use a different programming model and are not VBA code.
Frequently Asked Questions
What is the VBA equivalent of Excel SUBSTITUTE?
For replacing matching text in a VBA string, use the VBA Replace function. It returns a new string, so assign the result to a variable or cell. For direct worksheet changes, use a range’s Replace method.
How do I replace text in an entire column with VBA?
Call Replace on the column or on a defined used range, but consider limiting the range to the actual data. Set options such as LookAt and MatchCase explicitly, and check whether formulas in the range could be affected.
Is VBA Replace case-sensitive?
It can be. The string function’s comparison setting determines how it matches; specify vbTextCompare for case-insensitive matching or vbBinaryCompare for case-sensitive matching. For Range.Replace, set MatchCase to False or True.
Can VBA replace only the first occurrence?
Yes. In the string function, use the optional count argument to limit how many matches are replaced. A count of 1 limits the operation to one matching occurrence in that string.
Can I undo a VBA text replacement?
Usually not with Ctrl+Z. Macros that change worksheet contents clear or bypass Excel’s normal Undo history. Save a separate workbook copy before running replacement code so you can restore the original if the result is wrong.
Related
- Excel Shortcut to Select Cells to the End of a Column
- Excel Shortcut to Select the Active Cell’s Entire Column
- Excel Shortcut to Select to the Bottom of a Column
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA: Create a New Workbook with Workbooks.AddTo create a new workbook in Excel VBA, use Set wbNew = Workbooks.Add. This opens a workbook in…
- VBA Set Statement: How to Assign Object ReferencesIn VBA, Set is a statement—not a function—that assigns an object reference to an object variable. Use it…
- Excel VBA Text Color: Change Font and Cell Colors EasilyTo change text color in Excel VBA, set a range’s Font.Color property, usually with RGB(red, green, blue). For…
- Excel UserForm Images: Add, Size, and Fix Picture IssuesTo add an image to an Excel VBA UserForm, open the form in the Visual Basic Editor, place…
- Close an Excel Workbook with VBA Without Losing WorkTo close an Excel workbook with VBA, call its Close method and decide whether to save changes: choose…
- Excel VBA: How to Open a Binary FileYes. In Excel VBA on Windows, open a file with Open ... For Binary Access Read and use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.