Excel SUBSTITUTE and REPLACE Functions
last modified October 5, 2026
The SUBSTITUTE and REPLACE functions change the
contents of a text string. SUBSTITUTE replaces text by matching
it, while REPLACE replaces text at a given position. Together
they cover almost every find-and-replace task in Excel, from cleaning phone
numbers to reformatting product codes. This tutorial covers their syntax, the
exact rules for each argument, practical examples, and the most common
pitfalls.
Function Basics
SUBSTITUTE looks for specific text and swaps it for other text.
REPLACE swaps a counted range of characters that begins at a
given position. Both functions always return text, even when the result looks
like a number.
| Function | Description | Syntax |
|---|---|---|
| SUBSTITUTE | Replaces matching text with new text | =SUBSTITUTE(text, old_text, new_text, [instance_num]) |
| REPLACE | Replaces characters at a given position | =REPLACE(old_text, start_num, num_chars, new_text) |
This table summarizes the two functions. Optional arguments are shown in
brackets. Only instance_num is optional; every other argument is
required.
| Argument | Meaning |
|---|---|
| text | The string to change. It can be a literal, a cell reference, or an
expression such as A1&"-"&B1. Numbers are converted to
text automatically. |
| old_text | The text to find inside text. Matching is
case-sensitive. |
| new_text | The text that replaces old_text. An empty string
"" deletes the matched text. |
| instance_num | Optional. The occurrence of old_text to replace. If
omitted, every occurrence is replaced. |
REPLACE takes four required arguments of its own.
old_text is the string to change, start_num is the
1-based position of the first character to replace, num_chars is
the number of characters to replace, and new_text is the text
inserted in their place.
Three rules cover both functions. Positions start at 1, so a
start_num less than 1 produces a #VALUE! error, and
so does a negative num_chars or an instance_num of
0 or less. When an argument runs past the end of the string, the functions
adjust instead of failing: REPLACE replaces only the characters
that exist, and an instance_num larger than the number of
occurrences returns the text unchanged.
SUBSTITUTE vs REPLACE
The functions answer different questions. Choose SUBSTITUTE when
you know the text to change, and REPLACE when you know where it
sits.
| Feature | SUBSTITUTE | REPLACE |
|---|---|---|
| Works by | Matching text content | Position and character count |
| Default scope | All occurrences | Only the counted characters |
| Optional argument | instance_num targets one occurrence | None; all four arguments are required |
| Case sensitivity | Case-sensitive; a case mismatch means no replacement | Case never matters because it does not search |
| Wildcards | Not supported; * and ? are matched literally | Not supported |
| Typical use | Cleaning separators, renaming values, removing characters | Fixed-length codes, inserting or deleting at a known position |
Microsoft's own guidance follows this split: use SUBSTITUTE to
replace specific text wherever it occurs, and REPLACE to replace
text at a specific location. REPLACE is the better choice when
the text to replace varies but its position is predictable.
Basic SUBSTITUTE Example
This example demonstrates replacing a specific word in a text string.
=SUBSTITUTE("Hello World", "World", "Excel")
This formula replaces "World" with "Excel" in the string "Hello World". The
result is Hello Excel. Because no instance_num is
given, every occurrence of "World" would be replaced, but here there is only
one.
Replacing All Occurrences
By default, SUBSTITUTE replaces every occurrence of
old_text, not just the first one.
=SUBSTITUTE("banana", "a", "o")
The string contains three a characters, so all three become
o and the result is bonono. The replacement text can
be shorter, longer, or empty without any restriction on length. When
old_text matches the entire string, the whole text is swapped:
=SUBSTITUTE("apple", "apple", "pear") returns
pear.
SUBSTITUTE with Instance Number
The optional instance_num argument targets one specific
occurrence of old_text. This example replaces only the second
space in a string.
=SUBSTITUTE("A B C D", " ", "-", 2)
This formula replaces only the second space in "A B C D" with a hyphen. The
result is A B-C D; the first and third spaces are left alone.
Omitting instance_num would have replaced all three.
The following rules apply to instance_num. It must be 1 or
greater: 0 or a negative number produces a #VALUE! error. A
number larger than the number of occurrences returns the text unchanged, so
=SUBSTITUTE("banana", "a", "o", 4) returns banana
because the string holds only three a characters. The occurrences
are counted from left to right, as this example shows.
=SUBSTITUTE("Quarter 1, 2011", "1", "2", 3)
The text "Quarter 1, 2011" contains three 1 characters. The
third one is the last digit of the year, so the result is
Quarter 1, 2012.
Case Sensitivity and Wildcards
SUBSTITUTE is case-sensitive. If the case of
old_text does not match the case in the string, nothing is
replaced and no error is raised.
=SUBSTITUTE("apple", "Apple", "pear")
The formula looks for Apple with a capital A, finds
apple instead, and returns the original text unchanged. If a case
mismatch is possible, convert both sides with UPPER or
LOWER first, or locate the text with SEARCH, which
ignores case, and replace those characters by position with
REPLACE.
SUBSTITUTE does not support wildcards. The asterisk and question
mark are matched as ordinary characters, so
=SUBSTITUTE("a.b*c", "*", "-") returns a.b-c and
does not treat * as "any text". Every occurrence of the literal
character is replaced.
Basic REPLACE Example
This example shows how to replace characters at a specific position in a string.
=REPLACE("ABCDEFG", 3, 2, "XY")
The formula replaces 2 characters starting at position 3 in "ABCDEFG" with
"XY". Characters C and D are removed, so the result is ABXYEFG.
REPLACE never looks at the content of the replaced characters;
only the position and the count matter.
REPLACE with Cell Reference
This example demonstrates REPLACE with a cell reference, which is
how the function is normally used on fixed-length codes.
| A | B |
|---|---|
| Product123 | =REPLACE(A1, 8, 3, "456") |
=REPLACE(A1, 8, 3, "456")
Cell A1 contains "Product123". The three characters starting at position 8 are
"123", and the formula changes them to "456", returning
Product456. This is ideal for structured data such as product
codes, where the digits always sit in the same place.
Inserting Text with num_chars 0
When num_chars is 0, REPLACE deletes nothing and
simply inserts new_text at start_num. This turns
REPLACE into an insertion function.
=REPLACE("hello", 1, 0, "hi ")
Position 1 with a count of 0 inserts "hi " in front of the text, returning
hi hello. Insertion works at the end as well:
=REPLACE("abcdef", 7, 0, "X") returns abcdefX
because position 7 is just past the last character.
| Formula | Result | Reason |
|---|---|---|
| =REPLACE("abcdef", 5, 10, "") | abcd | Count runs past the end, so the rest is removed |
| =REPLACE("abcdef", 15, 4, "XY") | abcdefXY | start_num past the end appends new_text |
| =REPLACE("abc", 4, 2, "X") | abcX | Position one past the end appends too |
| =REPLACE("abc", 0, 1, "X") | #VALUE! | start_num must be 1 or greater |
These rows show the boundary behavior of REPLACE. The append
result in the second row surprises many users: when start_num
lies beyond the end of the text, new_text is added at the end
rather than the formula failing.
Removing Text with an Empty String
Both functions delete text when new_text is an empty string. The
difference is what gets deleted: SUBSTITUTE removes every match,
while REPLACE removes a counted range of characters.
=SUBSTITUTE("(555) 123-4567", "-", "")
The dash is deleted wherever it appears, returning
(555) 1234567. To remove by position instead, use
=REPLACE("abcdef", 1, 2, ""), which removes the first two
characters and returns cdef.
Cleaning Phone Numbers
Phone numbers mix both needs: separators must be stripped wherever they appear, and new separators must be inserted at exact positions. The table shows a typical pipeline.
| A | B | Result |
|---|---|---|
| (555) 123-4567 | =SUBSTITUTE(A1, "-", "") | (555) 1234567 |
| (555) 123-4567 | =SUBSTITUTE(SUBSTITUTE(A2, "-", ""), " ", "") | (555)1234567 |
| 5551234567 | =REPLACE(REPLACE(A3, 4, 0, "-"), 8, 0, "-") | 555-123-4567 |
The first two formulas strip separators with SUBSTITUTE: the
inner call removes the dashes, and the outer call removes the spaces. The
third formula formats a bare digit string with two REPLACE
insertions of count 0: a dash after the area code at position 4, then a dash
at position 8 of the enlarged string, giving 555-123-4567.
REPLACE suits the formatting step because digits have a fixed
layout.
Cleaning Dates Stored as Text
Text dates often arrive with inconsistent separators.
SUBSTITUTE normalizes them in one step.
=SUBSTITUTE("2024/03/15", "/", "-")
Every slash becomes a dash, returning 2024-03-15. The same idea
handles periods, spaces, or any other separator.
The formula works because the source is text. A real date cell stores a
serial number, so SUBSTITUTE would see digits such as 45366
instead of 2024/03/15. Convert the date to text first with
TEXT:
=SUBSTITUTE(TEXT(A1, "mm/dd/yyyy"), "/", "-")
If A1 holds the date March 15, 2024, TEXT renders it as
03/15/2024 and SUBSTITUTE then returns
03-15-2024.
Counting Occurrences with LEN and SUBSTITUTE
A useful trick counts how many times a character appears:
SUBSTITUTE removes all of them, and the length difference is the
count.
=LEN(A1)-LEN(SUBSTITUTE(A1, "-", ""))
If A1 contains "555-123-4567", the string is 12 characters long and the
version without dashes is 10 characters long, so the formula returns 2. The
count is also the largest valid instance_num, which makes the
trick handy before targeting the last occurrence of a separator.
Choosing Between SUBSTITUTE and REPLACE
The same task can often be done with either function. The table below replaces the first dash in "12-34-5678" with a slash in two different ways.
| Formula | Result | Approach |
|---|---|---|
| =SUBSTITUTE("12-34-5678", "-", "/", 1) | 12/34-5678 | First occurrence by content |
| =REPLACE("12-34-5678", FIND("-", "12-34-5678"), 1, "/") | 12/34-5678 | Position located with FIND |
| =SUBSTITUTE("12-34-5678", "-", "/") | 12/34/5678 | Every occurrence by content |
The first two rows return the same value. SUBSTITUTE is shorter
when the target text is unique or the first occurrence is wanted.
REPLACE earns its keep when the position is known without
searching, as in fixed-length codes. Replacing every occurrence is natural
with SUBSTITUTE and awkward with REPLACE.
Nested SUBSTITUTE Example
SUBSTITUTE performs a single replacement per call. To apply
several replacements in one formula, nest the calls.
=SUBSTITUTE(SUBSTITUTE("A*B#C", "*", "-"), "#", " ")
The inner call first replaces "*" with "-", producing "A-B#C". The outer call
then replaces "#" with a space, and the final result is A-B C.
The calls are evaluated from the inside out, so plan the order when one
replacement could create text targeted by another.
SUBSTITUTE and REPLACE Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| instance_num is 0 or negative | #VALUE! error | Use 1 or greater; the first occurrence is 1 |
| instance_num exceeds the occurrences | Text returned unchanged | Count occurrences with LEN minus LEN of the stripped text |
| Case does not match in old_text | Text returned unchanged, no error | SUBSTITUTE is case-sensitive; align case or normalize with UPPER or LOWER |
| old_text is not present at all | Text returned unchanged, no error | Check for typos and stray spaces before assuming success |
| start_num less than 1 in REPLACE | #VALUE! error | Remember that positions start at 1, not 0 |
| num_chars is negative | #VALUE! error | Use 0 to insert without deleting, or a positive count |
| start_num lies past the end of the text | new_text is appended to the end | Confirm the position with FIND or LEN before replacing |
| SUBSTITUTE applied to a real date or number | Serial digits are changed instead of the displayed text | Convert with TEXT before substituting |
The SUBSTITUTE and REPLACE functions are the two
halves of Excel's find-and-replace toolkit. SUBSTITUTE works by
content: it is case-sensitive, replaces every occurrence unless
instance_num narrows it down, and treats wildcards as ordinary
characters. REPLACE works by position: with
num_chars of 0 it inserts text, with an empty
new_text it deletes, and a start beyond the end appends.
Remember that both always return text, that a case mismatch or an oversized
instance_num fails silently, and that a start below 1 or an
instance_num of 0 raises #VALUE!. Combined with
FIND, LEN, and TEXT, they let you clean
codes, reformat phone numbers, and normalize dates in a single formula.
Author
List all Excel Formulas.