SUBSTITUTE Function

What is the SUBSTITUTE Function?

Most of us have watched the game of football or basketball. When the on-field player gets tired, the coach substitutes one of the replacements on the team.

The SUBSTITUTE function in Excel replaces a set of characters or a text string with another text string. However, before you replace any of the text strings in Excel, one of the most important parameters you need to consider is - what to replace.

You must input the same characters or text strings you need to match before another set of characters replaces them. The function is also very similar to the REPLACE function, which replaces a text string with a different one.

In this article, we will understand the syntax for the function, how to use it, and some of its examples.

Understanding The SUBSTITUTE function

The SUBSTITUTE is categorized as a text function that will replace an old text string with a new text string.

Suppose you have the text string 'apple' and need to replace the 'p' characters in the string with 'z.'

After using the formula, the result you will get equals' azzle.' It doesn't need to be just characters or text strings. You can also replace numbers and special symbols.

For example, if you have the phone number as 212-517-8949 and need to remove the dash( - ) character, you can use the function that will ultimately return the phone number as 2125178949.

The function is excellent, yes, but the only downside to using it is that it is case-sensitive and does not support the use of wildcards.

If you have the text string as 'Bonds are smart money and need to replace 'Bonds' with 'Stocks,' you need to use the same word with the same number of capitalized alphabets. This is because excel won't identify 'bonds,' 'BONDS,' or 'BONds.'

Similarly, you can't expect Excel to make a partial match using the function since it is not equipped to handle wildcard characters.

Next, we will see what the syntax for the function is and how it works.

SUBSTITUTE Function Formula

The syntax for the function:

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

where,

• text = (required)The text string will be evaluated to find the characters that need to be replaced.
• old_text = (required) The text that needs to be replaced
• new_text = (required) The text with which old_text will be replaced
• instance_num = (optional) Your text argument might consist of more than one recurring old_text. 

For example, if you wish to replace 'Hello' with 'Wassup' in the text string "Hello Tom, Hello Mike, Hello Jacob" and set the instance number to 3, then only the third old_text would be replaced with new_text.

The text string would be 'Hello Tom, Hello Mike, Wassup Jacob." If you ignore the instance_num argument, all the matching old_text would be replaced with the new_text, i.e., 'Wassup Tom, Wassup Mike, Wassup Jacob." 

How to use the SUBSTITUTE Function in Excel?

The SUBSTITUTE function can be used from the library or as a worksheet formula. We will explore both methods in this section.

Method 1: From the function's library

Functions are the predefined formulas where you need to input the arguments, and you directly get the result in the desired cell. To use the function, please follow the below steps:

1. First, select the cell in which you require the development.

2. Click on the Formulas tab > Text drop-down in the function library and select the SUBSTITUTE function.

3. This will open up the function's dialog box, as illustrated below:

4. Next, you just put all the arguments in the respective text boxes. You can also directly reference the cell values in those text boxes. For example, we have input the below ideas:

5. You might see the preview of our expected result as "Hi, My name is Jake." Finally, all you need to do is click on Ok, and you will get the result in the selected cell.

6. Since there was only one instance of 'Mike,' it was replaced by the new_text 'Jake.' 

Method 2: As a worksheet formula

To use the function as a worksheet formula, we begin with an equal sign, followed by the function name, and then input the arguments.

Suppose that you have the data in Excel, as illustrated below:

1. text - The text in which we need to replace the text strings is in cell C3. The formula becomes 

=SUBSTITUTE(C3,old_text,new_text,[instance_num])

2. old_text - The text that we minted to replace is 'homework.' We can directly reference the cell, i.e., C4, in the formula, which becomes 

=SUBSTITUTE(C3,C4,new_text,[instance_num] 

3. new_text - The text that we want to replace 'homework' is 'assignment.' By substituting the cell C5, the formula becomes

=SUBSTITUTE(C3,C4,C5,[instance_num] 

4. instance_num - Remember we said that if you skip the instance_num argument, all the old_text is replaced by the new_text. We will forget the discussion, so the final formula is

=SUBSTITUTE(C3, C4, C5) 

This will give us the result as illustrated below:

5. Both instances of substring 'homework' replaced 'assignment.'

6. Let's say we want to change the second instance to 'assignment.' In this case, the instance_num will be equal to 2 so that the formula becomes 

=SUBSTITUTE(C3, C4, C5, C6) 

This will give us a very different result, as illustrated below:

SUBSTITUTE Function Example

This section will see real-life scenarios where you can use the function. We will also see if there are any functions compatible with SUBSTITUTE.

1. Replacing all the space characters

Sometimes, you might need to replace all the space characters in the text string with a different unique character.

Suppose ten new employees joined your organization, and you need to generate their email ids.

In this case, you can concatenate a particular text string, say ‘@xyzcompany.com', and remove the space character by an underscore( _ )

The formula that we will use is =SUBSTITUTE(B3," ", "_")& "@xyzcompany.com," which will give you the result in cell C3 as illustrated below:

Drag down the formula up to the last cell, and the final result in all the cells will be:

2. Replacing different characters from phone numbers

One of the most common data parameters that you encounter is phone numbers. Phone numbers are everywhere, whether it be the employee database, the loan borrowers, Venture Capital networks, or fellow investment bankers.

However, the only problem you would face with those phone numbers is inconsistent formatting.

Some may include the international code before the numbers; others may use brackets or dash between the area and service provider code.

Suppose the phone numbers for loan borrowers are, as illustrated below:

As you can see, we have different formats for the numbers for the dataset. To get a uniform result devoid of space or special characters, we can use the formula, 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C3,"(",""),")",""),"-","")," ","")

This will give us the result:

Finally, all the phone numbers are in a consistent format!

So, how does the formula even works?

Let's say it is a type of loop. Then, to replace them with an empty string, we identify all the special characters, i.e., parenthesis, space, and dash.

Since the num_instance is ignored, the formula identifies all the instances of those special characters and returns the consistent format phone numbers.

For example, if we try to replace a dash with an empty string for the value in cell C3, we will get the result as (212) 4812137. The same effect is nested in another function with a different unique character, such as right side parenthesis.

After the substitutions are made, the result will be 212) 4812137. We can similarly go all the way to get the result as 2124812137 finally.

3. Count the number of repetitive instances

There could be situations you might need to determine how many times a particular character appears in a text string.

So what can you do about it?

You can use the combination of LEN and SUBSTITUTE functions in Excel.

Suppose that the data looks, as illustrated below:

Then, it would help to determine how many times the substring 'count' appear in the sentence.

The formula that we will use is =(LEN(C3)-LEN(SUBSTITUTE(C3, C4,"")))/LEN(C4), which will give us the number of substring 'count' in the sentence equal to 4.

How does the formula even work?

Let's break it down into three parts:

• LEN(C3) - The first LEN function gives us the length of the entire text string equal to 127.

• LEN(SUBSTITUTE(C3, C4," ”) - The second LEN combination finds our old_text 'count' and replaces it with an empty string, and then gives the length, which is equal to 107.
  We subtract his value from the original string length, i.e., LEN(C3)-LEN(SUBSTITUTE(C3, C4,""), which gives us the result of 20. The result is equal to the sum of all the characters of the instances found in our text string, i.e., count + count + count + count = 20.

• Finally, to get the count of the substring, we divide it by the length of the old_text, which is equal to 5. Thus, 20 divided by 5 gives us the result of 4, which is the total number of 'count' instances in our text string.

SUBSTITUTE vs. REPLACE function

Both functions are similar in functionality, i.e., replace a text string with a different one.

The difference between both functions is that the REPLACE function changes the characters in a particular string based on your input position. 

For example, if you have a text string as 'Apple' and need to replace 'A' with 'C.' You must input the position as 1 and the new_text as 'C.' When you count from the left-hand side, we know there are five characters in the text string.

On the other hand, the SUBSTITUTION function helps replace one or more instances because we know the text to replace beforehand.

The syntax for the REPLACE function is:

=REPLACE(old_text,start_num,num_chars,new_text)

where,

• old_text = (required) the main text string in which you need to replace the set of characters
• start_num = (required) the starting position of the character from which all the characters will be replaced in the old_text
• num_chars = (required) the number of characters that will be replaced
• new_text = (required) the text which will replace the set of characters beginning from start_num

Suppose you have the text string 'Hi Mike, Hello Jake.'

You want to replace 'Mike,' the fourth character in the text string(remember that space is also a character?). Hence, we input the start_num equal to 4. 

The text string we want to replace with 'Mike' is 'Dustin,' which is our new_text argument.

Finally, the most tricky argument that we believe is num_chars. Now, you can even replace a single character with thousands of characters, i.e., 'a' can be replaced by 'abcdefghijklmnopqrstuvwxyz,' and the function would work fine.

However, Excel only requires one input from you - input the number of characters you want to replace. If you input the number correctly, Excel should give you the exact answer you expect.

The formula that we will be using here is =REPLACE(C3, C4, C5, C6), which will give us the result:

Let's say we change the num_chars to 8. What do you think the result would be? Well, it would be something as illustrated below:

It doesn't make sense, right? So that's why we consider num_chars an essential argument in the function.

On the other hand, if you use the SUBSTITUTE function, the formula will be =SUBSTITUTE(C3, C4, C5), which should give you the result as illustrated below:

No hassles about finding the starting positions, you just input the old and new text to replace, and the function will do its work!

You can even control the number of instances when the function should replace a particular text string.

However, as we always say, both functions are essential in the Excel community and play a significant role in their way.

Researched and authored by Akash Bagul | LinkedIn

Reviewed and edited by Parul Gupta | LinkedIn

Free Resources

To continue learning and advancing your career, check out these additional helpful WSO resources:

• CODE Function
• HOUR Function
• PI Function
• YEAR Function
• YIELD Function