Using SUBSTITUTE function to replace text strings in Excel
Using SUBSTITUTE function to replace text strings in Excel: The SUBSTITUTE function in Excel is used to replace old text strings with new ones. Follow the article below to learn more about using the SUBSTITUTE function in Excel.
1. SUBSTITUTE function structure
Function syntax: =SUBSTITUTE(text, old_text, new_text, [instance_num])
Where:
- Text: a required argument, refers to the text or a reference to a cell containing the text that you want to replace characters in.
- Old_text: a required argument, is the text that you want to replace.
- New_text: a required argument, is the text that you want to replace old_text with.
- Instance_num: an optional argument, specifies the number of times you want to replace old_text with new_text. If you specify instance_num, only that occurrence of old_text is replaced. If you don’t specify instance_num, all occurrences of old_text in the text are replaced with new_text.
2. Using the SUBSTITUTE function
For example, suppose we have the following data table. We need to modify the Item Code according to the requirements below.
A. Replace “VT” with “HH”
To replace “VT” with “HH”, we have the formula for the first item code as follows:
=SUBSTITUTE(B2,”VT”,”HH”)
Drag the formula to the remaining cells to get the result:
Using SUBSTITUTE function to replace text strings in Excel
B. Replace all 0s in the item code with 9s
To replace all 0s in the item code with 9s, we have the formula for the first item code as follows:
=SUBSTITUTE(B2,0,9)
Drag the formula to the remaining cells to get the result:
Using SUBSTITUTE function to replace text strings in Excel
C. Replace the first 0 in the item code with 9
To replace the first 0 in the item code with 9, we have the formula for the first item code as follows:
=SUBSTITUTE(B2,0,9,1)
Drag the formula to the remaining cells to get the result:
Using SUBSTITUTE function to replace text strings in Excel
In this way, the article has guided you on how to use the SUBSTITUTE function in Excel. Hope this article will be helpful to you in your work. Wish you success!
