Excel Find and Replace with Formulas and VBA: SUBSTITUTE, Macros

Part of the free Module 3: Find and Replace · Lesson 6 of 6 · Full Excel course

Updated on 5 September 2026 · SUBSTITUTE, REPLACE and the VBA methods work in Excel 365, 2021, 2019 and 2016. TEXTSPLIT, TEXTBEFORE, TEXTAFTER and REGEXREPLACE need Microsoft 365.

Find and replace with Excel formulas and VBA covers the jobs the Ctrl+H dialog cannot do. SUBSTITUTE and REPLACE rewrite text in a new column while the original stays untouched and the result updates itself, TEXTSPLIT and REGEXREPLACE handle patterns in Excel 365, and the VBA Range.Replace method repeats a whole clean-up across every sheet or file with one click.

When to use a formula instead of Ctrl+H

The Replace dialog is a one-off edit: it changes the cells in place, there is no preview, and the next file needs the same clicks again. A formula keeps the source data, shows the result beside it, and recalculates when the source changes. A macro is the right tool when the same replacement must run on many sheets, many workbooks, or every week. Use the table to pick the method before you start.

Method Result updates itself Case sensitive Wildcards or patterns Many sheets or files Undo
Ctrl+H dialog No Optional (Match case) * ? ~ only Within: Workbook, one file at a time Ctrl+Z
SUBSTITUTE Yes Always No Copy the formula Delete the column
REPLACE Yes Not applicable (works by position) No Copy the formula Delete the column
REGEXREPLACE (365) Yes Optional argument Full regular expressions Copy the formula Delete the column
VBA Range.Replace No Optional (MatchCase) * ? ~ only Any loop you write None: save a copy first

SUBSTITUTE: replace by text

SUBSTITUTE finds a piece of text and swaps it for another, everywhere it occurs or only at one occurrence. It is the formula twin of the Replace dialog, with one important difference: it is always case sensitive.

Argument Meaning Example
text The cell or string to change C2
old_text The text to look for (case sensitive) "Assitant"
new_text The replacement; use "" to delete "Assistant"
instance_num Optional. Which occurrence to replace; omit for all 2
  • Fix a spelling mistake: =SUBSTITUTE(C2,"Assitant","Assistant")
  • Delete every space: =SUBSTITUTE(A2," ","")
  • Replace only the second hyphen: =SUBSTITUTE(A2,"-","/",2)
  • Several replacements in one cell, nested from the inside out: =SUBSTITUTE(SUBSTITUTE(A2,"Mr ",""),"Mrs ","")
  • Count how often a character appears: =LEN(A2)-LEN(SUBSTITUTE(A2,",","")) returns the number of commas.

Because SUBSTITUTE is case sensitive, wrap the text in UPPER or LOWER first when the source is inconsistent, or nest one SUBSTITUTE for each spelling. The full argument list and more examples are on the SUBSTITUTE function page.

REPLACE: replace by position

REPLACE does not care what the characters are, only where they sit. You give it a start position and a length, and it overwrites that slice with new text. Pair it with FIND or SEARCH when the position varies from cell to cell.

Argument Meaning Example
old_text The cell or string to change A2
start_num Position of the first character to replace (1 = first character) 4
num_chars How many characters to remove; 0 inserts without removing 3
new_text The text to put in their place "XXX"
  • Mask the last three digits of an ID: =REPLACE(A2,4,3,"XXX") turns 504424 into 504XXX.
  • Insert a hyphen after the third character without removing anything: =REPLACE(A2,4,0,"-") gives 504-424.
  • Replace everything after the first space: =REPLACE(B2,FIND(" ",B2)+1,LEN(B2),"Smith").
  • Change the first character only: =REPLACE(A2,1,1,"E").

REPLACE returns text, so a masked number is no longer a number. If the result must stay numeric, wrap it in VALUE or use MID and LEFT instead. See the FIND function and MID function pages for the position helpers.

TEXTSPLIT, TEXTBEFORE, TEXTAFTER and TEXTJOIN in Excel 365

Excel 365 and Excel 2024 add text functions that make many replace jobs unnecessary. Instead of removing the part you do not want, you extract the part you do. Each returns a dynamic array or a plain value and needs no helper column.

Function What it does Example on “PK – 504424” Result
TEXTBEFORE Returns the text before a delimiter =TEXTBEFORE(A2," - ") PK
TEXTAFTER Returns the text after a delimiter =TEXTAFTER(A2," - ") 504424
TEXTSPLIT Splits into columns or rows on a delimiter =TEXTSPLIT(A2," - ") PK | 504424
TEXTJOIN Joins pieces back together with a delimiter =TEXTJOIN(", ",TRUE,B2:B5) PK, Victor, King, Laura

Combine them to replace one item inside a delimited list. If A2 holds “Team Leader; Agent; Manager”, then =TEXTJOIN("; ",TRUE,SUBSTITUTE(TEXTSPLIT(A2,"; "),"Agent","Sales Agent")) splits the list, fixes the one entry and joins it again. In Excel 2021, 2019 and 2016 these functions return #NAME?. Flash Fill (Ctrl+E) is the closest alternative there, and wildcards in the dialog handle the rest.

REGEXREPLACE in Excel 365

Microsoft 365 (current channel, rolled out during 2024 and 2025) includes REGEXREPLACE, REGEXTEST and REGEXEXTRACT. A regular expression describes a pattern rather than fixed text, so one formula can remove every digit, collapse repeated spaces or reformat a phone number. The syntax is REGEXREPLACE(text, pattern, replacement, [occurrence], [case_sensitivity]). Occurrence 0 (the default) replaces every match; case_sensitivity 0 is case sensitive and 1 is not.

  • Remove every digit: =REGEXREPLACE(A2,"[0-9]+","") turns “PK 504424” into “PK “.
  • Collapse two or more spaces into one: =REGEXREPLACE(A2," {2,}"," ").
  • Keep only letters and spaces: =REGEXREPLACE(A2,"[^A-Za-z ]","").
  • Swap “Last, First” to “First Last”: =REGEXREPLACE(A2,"([A-Za-z]+), ([A-Za-z]+)","$2 $1"), where $1 and $2 refer to the bracketed groups.

If your Excel shows #NAME? for REGEXREPLACE, the function has not reached your build yet or you are on Excel 2021 or earlier. Nested SUBSTITUTE, or the VBA methods below, do the same work in every version.

VBA: the Range.Replace method

Range.Replace is the Replace All button as code. It works on any range, including ws.Cells for a whole sheet, and returns True whether or not anything changed. Paste this macro into a standard module (Alt+F11, Insert > Module) and run it with F5. It loops through every worksheet in the workbook and fixes the spelling mistake from the practice file.

Sub ReplaceOnAllSheets()
    Dim ws As Worksheet
    Dim n As Long
    Application.ScreenUpdating = False
    For Each ws In ThisWorkbook.Worksheets
        n = n + Application.WorksheetFunction.CountIf(ws.Cells, "Assitant Manager")
        ws.Cells.Replace What:="Assitant Manager", _
                         Replacement:="Assistant Manager", _
                         LookAt:=xlWhole, _
                         SearchOrder:=xlByRows, _
                         MatchCase:=False, _
                         SearchFormat:=False, _
                         ReplaceFormat:=False
    Next ws
    Application.ScreenUpdating = True
    MsgBox n & " cell(s) replaced across " & ThisWorkbook.Worksheets.Count & " sheet(s).", vbInformation
End Sub
Argument Values Notes
What Text, number or wildcard pattern Same * ? ~ rules as the dialog
Replacement Text or number; "" deletes Inserted literally
LookAt xlWhole or xlPart xlWhole = Match entire cell contents
SearchOrder xlByRows or xlByColumns Order only, never which cells
MatchCase True or False Match case in the dialog
SearchFormat, ReplaceFormat True or False Use with Application.FindFormat and Application.ReplaceFormat

Always state LookAt and MatchCase. Excel remembers the last values used by any macro or by the dialog, so a macro that omits them inherits whatever the previous search set, and the same sticky settings then appear in your Ctrl+H dialog. Range.Replace always looks in formulas, exactly like the dialog, so a formula that contains the search text is rewritten too. The Cells and Range lesson explains the objects used here.

VBA: Range.Find and a FindNext loop

When you need to inspect each match rather than replace it, use Range.Find followed by FindNext. Find returns the first matching cell, or Nothing when there is none, and FindNext continues from the last hit. Because the search wraps around, you must remember the first address and stop when it comes round again.

Sub ListAllAgents()
    Dim rng As Range, cel As Range
    Dim firstAddr As String, found As String
    Set rng = Worksheets("Sheet1").Range("C:C")
    Set cel = rng.Find(What:="Agent", LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)
    If cel Is Nothing Then
        MsgBox "No match found.", vbExclamation
        Exit Sub
    End If
    firstAddr = cel.Address
    Do
        found = found & cel.Address(False, False) & " = " & cel.Offset(0, -1).Value & vbNewLine
        Set cel = rng.FindNext(cel)
    Loop While Not cel Is Nothing And cel.Address <> firstAddr
    MsgBox found, vbInformation, "Agents"
End Sub

The test If cel Is Nothing is essential: calling .Address on a Nothing result raises run-time error 91. Set LookIn:=xlValues to search displayed results or xlFormulas to search formula text, the same choice as Look in. Loop patterns are covered in the Loops in VBA lesson.

VBA: replace in every workbook in a folder

The dialog searches only the active workbook. This macro opens each Excel file in a folder, runs the replacement on every sheet, saves and closes it. Type the folder path in cell A1 of the macro workbook, so no path is hard-coded.

Sub ReplaceInFolder()
    Dim folder As String, f As String
    Dim wb As Workbook, ws As Worksheet
    folder = ThisWorkbook.Worksheets(1).Range("A1").Value
    If Right(folder, 1) <> Application.PathSeparator Then folder = folder & Application.PathSeparator
    f = Dir(folder & "*.xls*")
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    On Error GoTo Done
    Do While f <> ""
        If folder & f <> ThisWorkbook.FullName Then
            Set wb = Workbooks.Open(folder & f)
            For Each ws In wb.Worksheets
                ws.Cells.Replace What:="Assitant Manager", Replacement:="Assistant Manager", _
                                 LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            Next ws
            wb.Close SaveChanges:=True
        End If
        f = Dir
    Loop
Done:
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    If Err.Number <> 0 Then MsgBox "Stopped on " & f & ": " & Err.Description, vbCritical
End Sub

There is no undo for a saved workbook, so run it on a copy of the folder first. The Workbooks lesson covers Open, Save and Close in more detail.

Worked example: fix, mask and count

Open the course practice file. Sheet1 holds the employee table; the Designation column contains “Assitant Manager” twice and “Agent” four times.

A: EMP ID B: Name C: Designation D: Salary E: Fixed F: Masked ID
2 504424 PK Team Leader 3,284 Team Leader 504XXX
3 417905 Victor Assitant Manager 4,866 Assistant Manager 417XXX
4 526956 King Manager 5,175 Manager 526XXX
5 517046 Laura Agent 2,593 Agent 517XXX
  1. In E2 type =SUBSTITUTE(C2,"Assitant","Assistant") and fill down. Column E shows the corrected titles while column C is untouched.
  2. In F2 type =REPLACE(A2,4,3,"XXX") and fill down. The first three digits stay, the last three are masked.
  3. In H1 type =SUMPRODUCT(--(C2:C12="Agent")) to count Agents; the result is 4.
  4. Press Alt+F11, insert a module, paste ReplaceOnAllSheets and press F5. The message box reports 2 cells replaced across 1 sheet, and column C now matches column E.
  5. Run ListAllAgents. The message box lists C6, C8, C9 and C10 with the names beside them.

Tips and common mistakes

  • SUBSTITUTE is case sensitive, the dialog is not. “agent” and “Agent” are different to SUBSTITUTE; normalise with LOWER or nest two calls.
  • REPLACE counts positions, not words. Use FIND to locate the start when it varies, and remember the first character is position 1, not 0.
  • Nested SUBSTITUTE runs inside out. Put the replacement that must happen first in the innermost call.
  • Convert results back to numbers. Every text function returns text; wrap in VALUE or multiply by 1 before summing.
  • Macros have no undo. Save the file before running Range.Replace, and test on a copy when the loop opens other workbooks.
  • State LookAt and MatchCase every time. Omitted arguments inherit the last search, and that last search may have been yours in the dialog an hour ago.
  • Range.Replace looks in formulas. Restrict the range to constants with ws.Cells.SpecialCells(xlCellTypeConstants) when formulas must not change.

Errors and how to fix them

Error or symptom Cause Fix
#VALUE! from REPLACE start_num is 0 or negative, or num_chars is negative Use 1 for the first character and check the FIND result
#VALUE! from FIND The search text is not in the cell Wrap in IFERROR, or use SEARCH which also ignores case
#NAME? for TEXTSPLIT or REGEXREPLACE Excel 2021, 2019 or 2016, or a Microsoft 365 build that does not have the function yet Use SUBSTITUTE, Flash Fill or the VBA macros
SUBSTITUTE changes nothing Case or an extra space does not match Check with =CODE and TRIM, or nest LOWER
Run-time error 91 in VBA Find returned Nothing and the code used it Test If cel Is Nothing before any property
Formulas broke after Range.Replace The method always searches formula text Replace on SpecialCells(xlCellTypeConstants) only
Run-time error 1004 on Replace The sheet is protected Unprotect it first with ws.Unprotect

Practice exercise

  1. Add a column that turns “Team Leader” into “TL” and “Assistant Manager” into “AM” with nested SUBSTITUTE, leaving other titles unchanged.
  2. Use REPLACE and FIND to change the first word of every name in column B to “Employee”.
  3. Count the total number of letter a’s (upper and lower case) in column B with LEN and SUBSTITUTE.
  4. Copy Sheet1 twice, introduce the typo on each copy, and run ReplaceOnAllSheets. Confirm the message box reports 6 replacements.
  5. Excel 365 only: rebuild column F with a REGEXREPLACE that masks every digit after the third, then compare it with the REPLACE version.

Key takeaways

  • SUBSTITUTE replaces by text and is case sensitive; REPLACE replaces by position.
  • Formulas keep the original and recalculate; the dialog and VBA change cells in place.
  • TEXTSPLIT, TEXTBEFORE, TEXTAFTER and REGEXREPLACE are Microsoft 365 functions; older versions show #NAME?.
  • Range.Replace is Replace All in code: set LookAt and MatchCase explicitly every time.
  • Range.Find returns Nothing when there is no match, so test it before using the result.
  • A Dir loop with Workbooks.Open extends the replacement to every file in a folder.

Related lessons

Frequently asked questions

What is the difference between SUBSTITUTE and REPLACE in Excel?

SUBSTITUTE looks for specific text and swaps it wherever it appears, so you use it when you know the characters but not their position. REPLACE overwrites a fixed number of characters starting at a given position, so you use it when you know where the change goes but not what is there, for example masking the last digits of an ID.

How do I replace multiple values at once with a formula?

Nest one SUBSTITUTE inside another, one per replacement: =SUBSTITUTE(SUBSTITUTE(A2,"TL","Team Leader"),"AM","Assistant Manager"). The inner call runs first. In Excel 365 you can also use REDUCE with LAMBDA over a two-column mapping table, or REGEXREPLACE with an alternation pattern, to handle longer lists.

How do I find and replace in VBA?

Call the Replace method on a range: Sheets("Sheet1").Cells.Replace What:="old", Replacement:="new", LookAt:=xlWhole, MatchCase:=False. Wrap it in a For Each loop over ThisWorkbook.Worksheets to cover every sheet. Always pass LookAt and MatchCase, because Excel reuses the previous search settings when they are omitted.

Is SUBSTITUTE case sensitive?

Yes. SUBSTITUTE only matches text with the same case, so “agent” is not replaced when old_text is “Agent”. The Find and Replace dialog ignores case unless Match case is ticked. To make SUBSTITUTE ignore case, convert the text with LOWER or UPPER first, or nest one SUBSTITUTE per spelling.

Does Excel have a regex replace function?

Microsoft 365 includes REGEXREPLACE, alongside REGEXTEST and REGEXEXTRACT, rolled out to the current channel during 2024 and 2025. Excel 2021, 2019 and 2016 do not have it and return #NAME?. In those versions, use nested SUBSTITUTE, Flash Fill, Power Query or a VBA macro with the VBScript RegExp object.

Want the finished version? Ready-made Excel dashboards, trackers and VBA systems are available at NextGenTemplates.com.

← PreviousFind and Replace Across Multiple Sheets and Workbooks in ExcelAll lessonsFind and Replace