Quick Navigation

SUBSTITUTE Function

Excel 2007+

Summary

The Excel SUBSTITUTE function replaces specific text within a larger text string. It targets and swaps designated text portions with new content, making it perfect for cleaning data, standardizing formats, and transforming strings dynamically.

Syntax

SUBSTITUTE(text, old_text, new_text, [instance_num])

Parameters

Parameter	Type	Required	Description
text	`Text`	Yes	The text or cell reference containing text for substitution
old_text	`Text`	Yes	Text substring to find and replace
new_text	`Text`	Yes	Replacement text for old_text
instance_num	`Number`	No	Specific occurrence number to replace (optional)

Using the SUBSTITUTE Function

SUBSTITUTE excels at text manipulation tasks like data cleaning, format standardization, and dynamic string modification. Use it to replace unwanted characters, update terminology across datasets, or create variations of text content. Unlike FIND or SEARCH which only locate text, SUBSTITUTE actively changes the string content.

Common SUBSTITUTE Examples

Basic Text Replacement

=SUBSTITUTE(A2, "Sales", "Revenue")

Changes 'Sales Data' in A2 to 'Revenue Data' by replacing all instances of 'Sales' with 'Revenue'.

First Instance Only

=SUBSTITUTE(A3, "1", "Q", 1)

In 'Quarter 1, 2008', replaces only the first '1' with 'Q' resulting in 'Quarter Q, 2008'.

Specific Occurrence

=SUBSTITUTE("Apple,Apple,Apple", "Apple", "Orange", 2)

Replaces only the second 'Apple' with 'Orange', yielding 'Apple,Orange,Apple'.

Data Cleaning

=SUBSTITUTE(B2, " USD", "$")

Standardizes currency display by replacing ' USD' with '$' in financial data.

Frequently Asked Questions

SUBSTITUTE returns the original text unchanged when instance_num is larger than the number of old_text occurrences.

Yes, SUBSTITUTE is case-sensitive. 'Apple' and 'apple' are treated as different text.

SUBSTITUTE finds and replaces specific text content. REPLACE changes text at exact character positions regardless of content.

Common Errors and Solutions

#VALUE! error

Cause: Non-numeric value supplied to instance_num parameter

Solution: Ensure instance_num is a positive integer or omit it entirely

No replacement occurs

Cause: old_text doesn't exist in the text string

Solution: Verify old_text matches exactly (including case) and exists in text

Notes

Omitting instance_num replaces ALL occurrences of old_text
Maximum 255 characters supported in most Excel versions
Useful for removing characters by substituting with "" (empty string)
Combines well with other text functions like LEFT, RIGHT, MID

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+