fx
FunctionPedia
Quick Answer

SUBSTITUTE Function

SUBSTITUTE replaces specific text within a string with new text.

✓ Excel✓ Google SheetsExcel All versions

Syntax

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

Parameters

ParameterDescriptionRequired
textThe text string in which to substitute characters.Required
old_textThe text to find and replace.Required
new_textThe text to replace old_text with.Required
instance_numOptional. 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 Tutorials

Convert Text to Number in Excel and Google Sheets

Convert text to number in Excel and Google Sheets with VALUE, multiplication and Paste Special. Handles currency symbols, hidden spaces and #VALUE! errors.

Remove Extra Spaces

TRIM, SUBSTITUTE and CLEAN remove extra spaces, non-breaking spaces and hidden characters in Excel and Google Sheets. Exact formulas plus lookup fixes.

Split Text By Delimiter

Split text by delimiter in Excel and Google Sheets — use TEXTSPLIT, SPLIT, LEFT with FIND, and Text to Columns to break names, emails and codes into columns.

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.