Quick Answer

SUBSTITUTE Function

SUBSTITUTE replaces specific text within a string with new text.

Syntax

SUBSTITUTE

(text, old_text, new_text, [instance_num])

Parameters

Parameter	Description	Required
text	The text string in which to substitute characters.	Required
old_text	The text to find and replace.	Required
new_text	The text to replace old_text with.	Required
instance_num	Optional. Which occurrence of old_text to replace.	Optional

Basic Example

Replace all occurrences

=SUBSTITUTE("apple, apple, banana", "apple", "orange")

Resultorange, orange, banana

Replaces all instances of "apple" with "orange".

Advanced Examples

Example 1: Replace only the first occurrence

Selective replacement

Replace just the first instance of text

=SUBSTITUTE("apple, apple, banana", "apple", "orange", 1)

Result: orange, apple, banana

Replaces only the first "apple" with "orange".

Example 2: Remove all spaces

Text cleaning

Delete all spaces from a text string

=SUBSTITUTE(A1, " ", "")

Result: helloworld

Replaces every space with nothing, effectively removing them.

How SUBSTITUTE Works

SUBSTITUTE searches for old_text in the text string and replaces it with new_text. If instance_num is specified, only that occurrence is replaced.

1

Select output cell

Choose where the result should appear.

2

Enter formula

Type =SUBSTITUTE(text, find_text, replace_text).

3

Press Enter

The result shows the modified text.

Important Notes & Limitations

SUBSTITUTE is case-sensitive.
For case-insensitive replacement, use UPPER/LOWER with SUBSTITUTE, or use REPLACE for position-based replacement.

Common Errors & Fixes

#VALUE!Invalid arguments

Fix: Ensure all required arguments are provided.

Download Practice File

Practice SUBSTITUTE with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the SUBSTITUTE function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Functions

CLEAN

Strips non-printable control characters from text.

CONCAT

CONCAT combines multiple text strings into a single string.

CONCATENATE

Joins text values together (legacy function).

FIND

Finds the position of text within another text string, case-sensitive.

LEFT

LEFT extracts a specified number of characters from the left side of a text string.

LEN

LEN counts the number of characters in a text string.

Frequently Asked Questions

Is SUBSTITUTE case-sensitive?

Yes, "Apple" and "apple" are treated as different.

What is the difference between SUBSTITUTE and REPLACE?

SUBSTITUTE replaces text by content; REPLACE replaces text by position.