SUBSTITUTE (replace text in a text string)

<< Click to Display Table of Contents >>

SUBSTITUTE (replace text in a text string)

Syntax:

SUBSTITUTE(Text, OldText, NewText [, n])

Description:

Searches for OldText in a text string. If found, OldText will be replaced by NewText.

Text is the given text string.

OldText is the text to be replaced. Note: This function is case-sensitive, i.e. it distinguishes between uppercase and lowercase letters.

NewText is the replacement text.

n (optional) lets you specify which occurrence of OldText to replace. If omitted, all occurrences will be replaced.

Example:

SUBSTITUTE("aabbaabb", "aa", "xx") returns xxbbxxbb

SUBSTITUTE("aabbaabb", "aa", "xx", 2) returns aabbxxbb

SUBSTITUTE("aabbaabb", "AA", "xx") returns aabbaabb

See also:

FIND, SEARCH, REPLACE, REPT