Excel SUBSTITUTE and REPLACE Functions
last modified April 4, 2025
The SUBSTITUTE and REPLACE functions are powerful
text manipulation tools in Excel. They allow you to modify text strings by
replacing specific characters or substrings. This tutorial provides a
comprehensive guide to using these functions with detailed examples.
Function Basics
SUBSTITUTE replaces specific text in a string, while
REPLACE changes text at a specific position. Both are essential
for text processing in Excel.
| Function | Syntax | Description |
|---|---|---|
| SUBSTITUTE | =SUBSTITUTE(text, old_text, new_text, [instance_num]) | Replaces specific text occurrences |
| REPLACE | =REPLACE(old_text, start_num, num_chars, new_text) | Replaces text at specific position |
The key difference is that SUBSTITUTE works with text content,
while REPLACE works with text position. Choose based on your needs.
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 will be "Hello Excel". This shows the simplest use of SUBSTITUTE.
SUBSTITUTE with Instance Number
SUBSTITUTE can target specific occurrences of text using the instance_num parameter. 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 will be "A B-C D". The instance_num parameter makes this precise.
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". The result will be "ABXYEFG". REPLACE works by position, not content.
REPLACE with Cell Reference
This example demonstrates REPLACE with a cell reference and dynamic positions.
| A | B |
|---|---|
| Product123 | |
| =REPLACE(A1, 8, 3, "456") |
The table shows how to replace part of a product code in cell A1. The formula in B2 replaces 3 characters starting at position 8 with "456".
=REPLACE(A1, 8, 3, "456")
If A1 contains "Product123", this formula changes it to "Product456". REPLACE is ideal for structured data like codes where position matters.
Nested SUBSTITUTE Example
Multiple SUBSTITUTE functions can be nested to perform several replacements. This example replaces multiple special characters in a string.
=SUBSTITUTE(SUBSTITUTE("A*B#C", "*", "-"), "#", " ")
This formula first replaces "*" with "-", then "#" with a space. The result is "A-B C". Nesting allows multiple replacements in one formula.
The SUBSTITUTE and REPLACE functions are essential
for text manipulation in Excel. SUBSTITUTE is best when you know the text to
replace, while REPLACE excels when you know the position. Mastering both will
greatly enhance your text processing capabilities.
Author
List all Excel Formulas.