What is use of SUBSTITUTE Formula?
The SUBSTITUTE Formula use in Microsoft Excel software. This formula used for replace or SUBSTITUTE one or multiple values with new values.
Syntax of SUBSTITUTE Formula
|
=SUBSTITUTE(text,
old_text, new_text) |
- text: This is the original value or text where you want to replace new value or text.
- old_text: The text you want to replace with new text.
- new_text: The text you want to replace with old_text.
Step-by-Step Guide for SUBSTITUTE Formula in Excel
Below is an example of using the SUBSTITUTE formula. In this
example, we replace the value 'XYZ' with the product column value using the
formula.
|
A |
B |
C |
|
AAA |
XYZ is best product in
U.S. |
AAA is best product in
U.S. |
|
BBB |
XYZ is best product in U.S. |
=SUBSTITUTE(B2,
“XYZ”, A2) |
|
CCC |
XYZ is best product in
U.S. |
=SUBSTITUTE(B3, “XYZ”, A3) |
|
DDD |
XYZ is best product in U.S. |
=SUBSTITUTE(B4,
“XYZ”, A4) |
|
EEE |
XYZ is best product in
U.S. |
=SUBSTITUTE(B5, “XYZ”, A5) |
|
FFF |
XYZ is best product in U.S. |
=SUBSTITUTE(B6,
“XYZ”, A6) |
|
GGG |
XYZ is best product in
U.S. |
=SUBSTITUTE(B7, “XYZ”, A7) |
|
HHH |
XYZ is best product in U.S. |
=SUBSTITUTE(B8,
“XYZ”, A8) |
|
III |
XYZ is best product in
U.S. |
=SUBSTITUTE(B9, “XYZ”, A9) |
|
JJJ |
XYZ is best product in U.S. |
=SUBSTITUTE(B10,
“XYZ”, A10) |
|
KKK |
XYZ is best product in
U.S. |
=SUBSTITUTE(B11, “XYZ”, A11) |
|
LLL |
XYZ is best product in U.S. |
=SUBSTITUTE(B12,
“XYZ”, A12) |
- Select the cell where you want the modified product code to appear (e.g., C2).
- Enter the formula where you want to modified sentence.
|
=SUBSTITUTE(B2, “XYZ”, A2) |
- Here, B2 is the reference to the original product code in cell A2.
- "XYZ" is the text you want to replace with new text.
- "A2" cell text is the text you want to replace "XYZ" with.
3. Press Enter. Excel will show the modified product code based on your formula (Cell C1)
Now you know how to use the SUBSTITUTE function in Excel to
replace specific text within a cell or range of cells. Test with
different scenarios to leverage its full potential.
This step-by-step guide should help you effectively use the SUBSTITUTE formula in Excel for various text manipulation tasks.
0 Comments