Replace one or many instances of specific text based on its content (not on its position).

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

---

Replace one or many instances of specific text using either defined values (e.g. "string") or cell references (e.g. C3).

SUBSTITUTE syntax requires the string or cell reference to which the SUBSTITUTE function is to be applied, the text to be removed, the text to replace the old_text with and (optionally) how many instances of old_text to replace with new_text.

---

SUBSTITUTE with a text string.

In the example below, the SUBSTITUTE function is used to replace specific text. Note the text is case sensitive.

---

SUBSTITUTE with a number.

In the example below, the SUBSTITUTE function is used to replace specific numbers for rounding purposes. The TEXT function is also applied to format the numbers with currency symbols and thousand commas.

---

Syntax

The SUBSTITUTE function consists of 4 arguments:

1. text. Required. The string or cell reference to which the SUBSTITUTE function is to be applied.
2. old_text. Required. The text to be removed.
3. new_text. Required. The text to replace the old_text with.
4. instance_num. Optional. Specifies how many instances of old_text to replace with new_text. Default is all instances are replaced.

---

Try it now!

Enter your formulas into the grey cells.

---

Scope

SUBSTITUTE() can be applied to cell references, text or numbers.

Cell reference:	=SUBSTITUTE(C3,"South","North",1)
Text:	=SUBSTITUTE("South America","South","North") || returns North America.
Number:	=SUBSTITUTE(123456,123,789 ) || returns the number 789456.

Caveats

• If no instance_num is used, all occurrences of old_text will be replaced by new_text.
• SUBSTITUTE() is case-sensitive and does not support wildcards.
• new_text can be 2 double quotation marks ("") to replace old_text with nothing.

---

Related Functions

• Use the REPLACE() function when you want to replace any text that occurs in a specific location in a text string.
• The TRIM() function removes irregular spacing from either end of or within a text string.

Functions Category

• Text