SUBSTITUTE

TextExcel 2007

Replace every (or the Nth) occurrence of a piece of text.

Syntax

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

Arguments

text: The original string.
old_text: The text to replace.
new_text: What to put in its place.
instance_numoptional: Replace only this occurrence; omit to replace all.

Examples

=SUBSTITUTE("2,00,000", ",", "")

→ 200000

=SUBSTITUTE(A2, " ", " ")

→ double → single space

Good to know

SUBSTITUTE works on text (match a string); REPLACE works on position (match a location).

Related functions

REPLACE TEXT TRIM

More Text functions

ARRAYTOTEXT ASC BAHTTEXT CHAR CLEAN CODE CONCAT CONCATENATE DBCS DOLLAR EXACT FIND

← All 510 functions

Syntax follows Microsoft’s documentation. Examples are written to be pasted into a cell and checked. Argument separators may be ; in your locale. The AI explainer runs on a free community model — verify anything important.