Excel SUBSTITUTE Function
The Excel SUBSTITUTE function replaces characters with another in a specified string
Example: Excel SUBSTITUTE Function
|
=SUBSTITUTE("a1a2a3a4a5","a","b")
|
Result in cell F5 (b1b2b3b4b5) - replaces all of the occurrences of character "a" with character "b" in the string.
|
|
=SUBSTITUTE("a1a2a3a4a5","a","b",2)
|
Result in cell F6 (a1b2a3a4a5) - replaces only the second instance of character "a" with character "b" in the sting.
|
|
=SUBSTITUTE("I'm good at tennis","good","bad")
|
Result in cell F7 (I'm bad at tennis) - replaces all of the instances of "good" with "bad" in the string. Given there is only one occurrence of the the old text (good), the formula only replaces this occurrence with the new text (bad).
|
|
=SUBSTITUTE(B5,C5,D5)
|
Result in cell F5 (b1b2b3b4b5) - replaces all of the occurrences of the character in cell (C5), which is "a", with the character in cell (D5), which is "b", in the selected string (cell (B5)).
|
|
=SUBSTITUTE(B6,C6,D6,E6)
|
Result in cell E6 (a1b2a3a4a5) - replaces only the second instance of the character in cell (C6), which is "a", with the character in cell (D6), which is "b", in the selected string (cell (B6))
|
|
=SUBSTITUTE(B7,C7,D7)
|
Result in cell E7 (I'm bad at tennis) - replaces all of the instances of the text in cell (C7), which is "good", with the text in cell (D7), which is "bad", in the selected string (cell (B7)). Given there is only one occurrence of the the old text (good), the formula only replaces this occurrence with the new text (bad).
|
METHOD 3. Excel SUBSTITUTE function using the Excel built-in function library with hardcoded values
EXCEL
| =SUBSTITUTE("a1a2a3a4a5","a","b",2) Note: in this example we are replacing the second instance of character "a" with character "b" in the specified text string (a1a2a3a4a5). |
 |
METHOD 4. Excel SUBSTITUTE function using the Excel built-in function library with links
EXCEL
| =SUBSTITUTE(B6,C6,D6,E6) Note: in this example we are replacing the second instance of the character in cell (C6), which is "a", with the character in cell (D6), which is "b", in the specified text string in cell (B6), which is a1a2a3a4a5. |
 |
Dim ws As Worksheet
ws.Range("F5") = Application.WorksheetFunction.Substitute("a1a2a3a4a5", "a", "b")
ws.Range("F6") = Application.WorksheetFunction.Substitute("a1a2a3a4a5", "a", "b", 2)
ws.Range("F7") = Application.WorksheetFunction.Substitute("I'm good at tennis", "good", "bad")
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SUBSTITUTE.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("F5"), ("F6") and ("F7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
Dim ws As Worksheet
ws.Range("F5") = Application.WorksheetFunction.Substitute(ws.Range("B5"), ws.Range("C5"), ws.Range("D5"))
ws.Range("F6") = Application.WorksheetFunction.Substitute(ws.Range("B6"), ws.Range("C6"), ws.Range("D6"), ws.Range("E6"))
ws.Range("F7") = Application.WorksheetFunction.Substitute(ws.Range("B7"), ws.Range("C7"), ws.Range("D7"))
End Sub
Worksheets: The Worksheets object represents all of the worksheets in a workbook, excluding chart sheets.
Range: The Range object is a representation of a single cell or a range of cells in a worksheet.
Worksheet Name: Have a worksheet named SUBSTITUTE.
String: Have the string in which you want to replace characters in range ("B5:B7").
Old Text: Have the characters that you want to replace in range ("C5:C7").
New Text: Have the characters that you want to replace with in range ("D5:D7").
Instances: Have the instance of the characters that you want to replace in range ("E5:E7").
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell references ("F5"), ("F6") and ("F7") in the VBA code to any cell in the worksheet, that doesn't conflict with formula.
String: Select the string in which you want to replace character by changing the range ("B5:B7") to any range in the worksheet, that doesn't conflict with the formula.
Old Text: Select the characters that you want to replace by changing the range ("C5:C7") to any range in the worksheet, that doesn't conflict with the formula.
New Text: Select the characters that you want to replace with by changing the range ("D5:D7") to any range in the worksheet, that doesn't conflict with the formula.
Instances: Select the instances of the characters that you want to replace by changing the range ("E5:E7") to any range in the worksheet, that doesn't conflict with the formula.
The Excel SUBSTITUTE function replaces characters with another in a specified string.
=SUBSTITUTE(text, old_text, new_text, [instance_num])
text: (Required) The string from which to subtract characters.
old_text: (Required) The existing characters to replace.
new_num: (Required) The new characters to replace.
instance_num: (Optional) The kth instance of the old_text to be replaced with the new_text.
ADDITIONAL NOTES
Note 1: If the instance_num argument is omitted all instances of the old_text will be replaced with the new_text.
Note 2: The Excel SUBSTITUTE function does not support wildcards.
Note 3: The Excel SUBSTITUTE function is case-sensitive.