SUBSTITUTE

Substitutes new text for old text in a text string.

Syntax:

SUBSTITUTE(originaltext, oldtext, newtext, which)

In originaltext, removes oldtext, inserts newtext in its place, and returns the result. oldtext and newtext can have different lengths.

which (optional) is a number which specifies which occurrence of oldtext to replace (counting from the left). If omitted, all occurrences are replaced.

Example:

SUBSTITUTE("castle", "stl", "v")

returns cave.

SUBSTITUTE("a red red rose", "red", "white")

returns a white white rose. All occurrences of red are replaced if which is omitted.

SUBSTITUTE("a red red rose", "red", "and white", 2)

returns a red and white rose. The second occurrence of red is replaced.

Application:

Let's say you have a spreadsheet with a list of product names, and you need to standardize them. Some of the entries use "U.S.A." while others use "USA". You want all of them to be "USA".

Here's a simple example using the SUBSTITUTE function:

Initial Data

	Product Name
	A
1	Laptop (made in U.S.A.)
2	Smartphone (made in USA)
3	Tablet (U.S.A. version)
4	Desktop (USA model)

In this table, the goal is to replace "U.S.A." with "USA" in column A and display the result in column B.

Using the SUBSTITUTE function

In cell B2, you would enter the following formula:

SUBSTITUTE(A2, "U.S.A.", "USA")

Let's break down the arguments of the function:

A2: This is the text where you want to perform the substitution.
"U.S.A.": This is the old_text you want to find.
"USA": This is the new_text you want to replace it with.

Final Result

	Product Name	Corrected Name
	A	B
1	Laptop (made in U.S.A.)	Laptop (made in USA)
2	Smartphone (made in USA)	Smartphone (made in USA)
3	Tablet (U.S.A. version)	Tablet (USA version)
4	Desktop (USA model)	Desktop (USA model)

As you can see, the SUBSTITUTE function successfully found every instance of "U.S.A." and replaced it with "USA", while leaving the entries that already had "USA" unchanged. This is a common and practical use case for cleaning and standardizing text data.