VBA Replace in Excel — Swap Text & the Traps in Its Arguments

Tested on: Excel 365 v2509 · Excel 2021 · Excel 2019 · last verified 2026-06-09

TL;DR — Replace finds a substring and swaps it for another — every occurrence, in one pass. It is Ctrl+H written in code, and it returns a new string rather than changing the original:

Sub ReplaceDemo()
    Dim s As String
    s = "2026-04-01"
    Debug.Print Replace(s, "-", "/")     ' 2026/04/01   <- ALL dashes, one call
    Debug.Print s                        ' 2026-04-01   <- original is untouched
End Sub

The full signature, where the trouble lives:

Replace(expression, find, replace, [start], [count], [compare])
'                                    ⚠ traps      vbTextCompare = case-insensitive

The mental model: it's Ctrl+H, not "swap the first one"

When you press Ctrl+H in Excel and hit "Replace All," it changes every match at once. The VBA Replace function does exactly that, by default — there is no "find next, ask me" mode. Hand it a string and it returns a copy with every occurrence swapped.

Two things fall straight out of that model. First, it does not touch the original variable — Replace is a function that returns the result, so you must capture it (s = Replace(s, …)). Second, because it changes everything, Replace is the right tool for blanket transformations — normalise all separators, strip all $, swap all "N/A" for "" — and the wrong tool when you only mean to change one specific occurrence. We'll come back to that, because the "change only the first" path is where Replace's arguments turn into a data-loss trap.

The one rule: Replace is case-sensitive by default

This is the rule behind half the "my Replace does nothing" questions on the internet:

By default, Replace matches with vbBinaryCompare — exact case. Replace("Hello", "h", "J") returns "Hello" unchanged, because capital H is not lowercase h.

To match regardless of case, you must pass the sixth argument explicitly:

Debug.Print Replace("Hello", "h", "J")                      ' Hello   (no match!)
Debug.Print Replace("Hello", "h", "J", , , vbTextCompare)   ' Jello   (matched H)

Note the two empty commas — you are skipping start and count to reach compare. That awkwardness is a hint from the language: those middle arguments are ones you usually want to leave alone. Which brings us to the real trap.

The trap that eats your data: start truncates the result

Everyone reads the start argument as "begin searching from this position." It does that — and also throws away everything before it. The string Replace returns begins at start:

Debug.Print Replace("ABCDEF", "C", "x", 3)
' You expect: "ABxDEF"
' You get:    "xDEF"        <- "AB" is GONE

Starting at position 3 means the returned value starts at character 3 ("CDEF"), with the replacement applied → "xDEF". The first two characters are not skipped over and preserved — they are dropped from the output entirely. On a one-off this is obvious; buried in a function that processes 50,000 cells, it silently shortens every value and you find out when totals don't reconcile.

The count argument (max number of replacements) is less destructive but interacts with start in exactly the way you don't want when your real goal is "just the first one." The honest rule: leave start and count at their defaults. If you genuinely need to replace only the first occurrence, don't reach for count — locate it with InStr and rebuild with Mid:

' Replace only the FIRST "-" without losing the prefix
pos = InStr(s, "-")
If pos > 0 Then s = Left(s, pos - 1) & "/" & Mid(s, pos + 1)

More code than Replace(s, "-", "/", , 1) — but it doesn't eat the front of your string.

The other Replace: Range.Replace changes the sheet, not a string

There are two completely different "Replace" in Excel VBA, and mixing them up wastes hours. The function (Replace(...)) transforms a string in memory. The method (Range.Replace ...) is Find & Replace on the worksheet itself:

' Method — rewrites cells directly, whole column in one call, no loop
Columns("B").Replace What:="N/A", Replacement:="", _
                     LookAt:=xlWhole, MatchCase:=False

If your goal is "change this text across a range of cells," do not loop cell by cell calling the string function. Range.Replace does the entire range in one shot and is dramatically faster on large sheets. Reach for the string function only when you are manipulating a value you already hold in a variable.

The opinion: those optional arguments are traps, not features

VBA's Replace has six parameters, and the design quietly tells you to use three. expression, find, replace — yes. compare — sometimes, when case matters. start and count — almost never, because their behaviour (truncating the result, awkward interaction) surprises everyone and silently corrupts data.

My rule of thumb: if you're typing the fourth or fifth argument to Replace, stop and ask whether you actually want InStr + Mid instead. Nine times out of ten you wanted "replace the first occurrence" or "replace from here on," and the position-based surgery is both clearer and safe. The two-argument-skip vbTextCompare call is the only "advanced" form of Replace worth keeping in muscle memory.

When to use which

Common Replace mistakes (and the fix)

When the text cleanup becomes the job — describe the outcome instead

One Replace is trivial. A real cleanup is a stack of them — strip the $, swap the thousands separators, normalise "N/A", fix the date dashes, trim the residue — applied across a sheet, in the right order, without clobbering values that happen to contain a dollar sign legitimately. ExcelMaster Agent lets you describe the end state — "clean column C: remove currency symbols, blank out N/A, standardise the date format" — and generates Python that does it safely and backs up your file first, no start-argument surprises. Try it free →

Related guides

• VBA Split — Turn One String Into an Array
• VBA Mid, Left & Right — Extract a Substring by Position
• VBA InStr — Find Text Inside a String
• VBA For Loop in Excel — 8 Real-World Examples

FAQ

What does Replace do in VBA? The Replace function returns a copy of a string with every occurrence of one substring swapped for another. Replace("a-b-c", "-", "/") returns "a/b/c". It does not change the original variable — you must assign the result back.

Why is my VBA Replace not working / not replacing anything? The most common cause is case. By default Replace is case-sensitive (vbBinaryCompare), so Replace("Hello", "h", "J") finds no match. Pass vbTextCompare as the sixth argument to match regardless of case. The second most common cause is forgetting to capture the return value.

How do I make VBA Replace case-insensitive? Add vbTextCompare as the compare argument: Replace(s, find, repl, , , vbTextCompare). The two empty commas skip the start and count arguments, which you should normally leave at their defaults.

Why does VBA Replace cut off the start of my string? Because you passed the start argument. Replace returns a string that begins at start — everything before that position is dropped, not preserved. To replace within a string without losing the prefix, leave start out, or use InStr + Mid.

What's the difference between Replace and Range.Replace? Replace(...) is a function that transforms a string in memory. Range.Replace ... is a method that performs Find & Replace directly on worksheet cells across a whole range in one call. Use the method to edit the sheet; use the function to edit a string variable.