Excel SUBSTITUTE Function

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, old_text, new_text, [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_reynolds@marvel.com	123-123-1234
3	Robert Downey Jr.	rdj@marvel.com	123-543-3245
4	Scarlett Johansson	sjohansson@marvel.com	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_reynolds@excelexercises.com - 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.