Excel SUBSTITUTE Function

The SUBSTITUTE function in Excel is used to replace a specific section (or "substring") of text with other specified text. It can be used to replace every instance of the substring: for example, if you have text describing a meeting that happens every Friday and the meeting is moved to Thursday, you can use SUBSTITUTE to switch each "Friday" for "Thursday" in the text. Optionally, you can replace only a certain instance of the substring, like the 3rd "Friday" in the text.

Syntax of the SUBSTITUTE Function

The SUBSTITUTE function has 3 required arguments (input data separated by commas), and an optional fourth argument which specifies which instance of the substring to replace. The syntax is as follows:

=SUBSTITUTE(text, oldtext, newtext, [nth_appearance])

text (required) \- This is a text value or reference to a cell in your Excel spreadsheet which contains text. This is the text that SUBSTITUTE will search for the substring specified by old\_text.
old\text (required) \- The old\text are the characters or text you want replaced in the cell selected, it can be one character or a set of continuous characters that you specify to be substituted. The text in the old\_text argument can contain any type of characters.
new\text (required) \- The new\text is a character or set of characters you specify that will replace old\_text.
nth\appearance (optional) \- The nth\appearance is the instance of old\text you specify to replace. The instance limits the substitution to a specific nth\appearance of the old\text, if this argument is omitted SUBSTITUTE will replace all the instances of old\text.

Important Note: Text values in Excel, such as old\text and new\text must be surrounded by double quotation marks (" "). Text values used in SUBSTITUTE are case sensitive. SUBSTITUTE does not support wildcard matching.

Examples using the SUBSTITUTE Function in Excel

Use this data for the following function examples.

	A	B	C
1	Name	Email	Phone Number
2	Ryan Reynolds	ryan\[email protected]	123-123-1234
3	Robert Downey Jr.	[email protected]	123-543-3245
4	Scarlett Johansson	[email protected]	123-987-5432

Using SUBSTITUTE to Update an Email Address

Say we want to give each actor an Excel Exercises email. We could use the SUBSTITUTE function to replace "marvel" with "excelexercises".

=SUBSTITUTE(B2, "marvel", "excelexercises")

text: B2 \- The text in cell B2 is the value that the SUBSTITUTE function will search for old\_text.
old\text: "marvel" \- This is the text that the SUBSTITUTE function will search for and replace with new\text. Excel will search for all the instances of "marvel" in the cell B2.
new\text: "excelexercises" \- This is the text we would like to replace the old\text.
nth\appearance: omitted \- We left this blank because the text argument doesn't contain more than one instance of the old\text argument.

The substitute function would return ryan\[email protected] - it would replace the text "marvel" in the email address with "excelexercises", giving Ryan an Excel Exercises email address.

Using the SUBSTITUTE Function to Update a Phone Number Format

If we wanted to change the phone number format, we could write:

=SUBSTITUTE(C2, "-", " ", 1)

text: C2 \- Select the cell C2. This is the value that the SUBSTITUTE function will search for old\_text.
old\_text: "-" \- This is the character in cell C2 which you specify to substitute. Excel will look for all the instances of "-" in the cell C2.
new\text: " " \- This is the character or set of characters you specify to replace the old\text.
nth\appearance: 1 \- This specifies which instance of the old\text you want substitute for the new\_text. We supplied a value of 1 here because we only want to replace the first "-".

The substitute function would return 123 123-1234. It would replace only the first dash "-" in the phone number with a space " " because we included the fourth argument, 1. Because the phone number contains multiple instances of old\_text "-" the 1 ensures that we're only replacing the first instance.

If we wanted to substitute all the dashes "-" for empty space " " for the phone number of Ryan Reynolds in cell C2, we could write:

=SUBSTITUTE(C2, "-", "")

text: C2 \- Select the cell C2. This is the value that the SUBSTITUTE function will search for old\_text.
old\_text: "-" \- This is the character(s) in the text you want to replace. Excel will look for all the instances of "-" in the cell C2.
new\text: " " \- This is the character in the new text string " " you want to replace the old\text "-".
nth\appearance: omitted \- Leaving it blank will not define an instance to substitute, therefore it will substitute all the instances of old\text.

The substitute function would return 123 123 1234 from what was originally 123-123-1234. It would replace all the dashes "-" for a space " " in the phone number because the nth\_appearance is left undefined.

Using SUBSTITUTE to Update an Area Code in Excel

If we wanted to substitute only the first three numbers "123" for "785" for the phone number of Ryan Reynolds in cell C2 , we could write:

=SUBSTITUTE(C2, "123", "785", 1)

text: C2 \- The text in which we're searching for old\_text.
old\_text: "123" \- This is the character(s) in the text you want to replace. Excel will look for all the instances of "123" in the cell C2; in this case there's two instances and we only want to replace the first one.
new\text: "785" \- This is the new set of characters "785" you want to substitute for the old\text "123".
nth\appearance: 1 \- This specifies that you want to replace only the first set of characters "123" in cell C2. old\text appears twice but we only want to replace the area code (the first instance).

The substitute function would return 785-123-1234 from what was originally 123-123-1234. It would substitute only the first instance of "123" with "785" in the phone number as specified in the nth\_appearance.

Using SUBSTITUTE to Update Spelling of Text in Excel

If we wanted to substitute only the second "t" in Scarlett Johansson's first name for an "s" in cell A4, we could write:

=SUBSTITUTE(A4, "t", "s", 2)

text: A4 \- This is the value that the SUBSTITUTE function will search for old\_text.
old\_text: "t" \- This is the character in the text you want to substitute. Excel will look for all the characters "t" in the cell A4.
new\text: "s" \- This is the character in the new character "s" you want to replace the old\text "t".
nth\appearance: 2 \- This specifies that we want the second instance of the old\text "t" to be substituted for the new\_text "s"

The substitute function would return "Scarlets Johansson" from what was originally "Scarlett Johansson". It would replace the second instance of the character "t" for an "s" as especified in the nth\_appearance. The first instance of "t" remains unchanged. If the name contained any additional instances of "t", these would also remain unchanged. Continue to SUBSTITUTE practice exercises! →

Images Referenced

https://excelexercises.com/logo2.png
https://excelexercises.com/excel-functions/excelImages/logoGreenWhite.png