Table of Contents
The Substitute function in Microsoft Excel allows users to replace specific text within a cell with a new value. This can be helpful when trying to make multiple substitutions within a single cell. To do this, the user must specify the original text, the text to be replaced, and the new text to replace it with. This function can save time and effort when dealing with large amounts of data, as it eliminates the need for manually making individual substitutions. It is a useful tool for efficient data management and manipulation in Excel.
Excel: Substitute Multiple Values in Cell
You can use the following formula to substitute multiple values in a cell in Excel:
=SUBSTITUTE(SUBSTITUTE(A1,"oldtext1","newtext1"),"oldtext2","newtext2")This particular formula makes the following substitutions in the text in cell A1:
- oldtext1 is replaced with newtext1
- oldtext2 is replaced with newtext2
Note that in this example we substitute two values in a cell, but you can create as many nested SUBSTITUTE functions as you’d like to substitute as many values as you’d like.
The following example shows how to use this formula in practice.
Example: Substitute Multiple Values in Cell in Excel
Suppose we have the following dataset in Excel that contains information about various basketball players:
We can use the following formula to substitute “Guard” with “Gd.” and “Forward” with “Fd.” for each value in the Position column:
=SUBSTITUTE(SUBSTITUTE(B2,"Guard","Gd."),"Forward","Fd.")
We’ll type this formula into cell C2 and then click and drag it down to each remaining cell in column C:
Notice that the following substitutions were made in each cell in column A:
- The string “Guard” was replaced with “Gd.”
- The string “Forward” was replaced with Fd.”
Note: If none of the strings in the formula are found in the text that you search, then the formula will simply return the original text without any substitutions.
Cite this article
stats writer (2024). How can I substitute multiple values in a cell using Excel’s Substitute function?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-substitute-multiple-values-in-a-cell-using-excels-substitute-function/
stats writer. "How can I substitute multiple values in a cell using Excel’s Substitute function?." PSYCHOLOGICAL SCALES, 22 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-substitute-multiple-values-in-a-cell-using-excels-substitute-function/.
stats writer. "How can I substitute multiple values in a cell using Excel’s Substitute function?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-substitute-multiple-values-in-a-cell-using-excels-substitute-function/.
stats writer (2024) 'How can I substitute multiple values in a cell using Excel’s Substitute function?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-substitute-multiple-values-in-a-cell-using-excels-substitute-function/.
[1] stats writer, "How can I substitute multiple values in a cell using Excel’s Substitute function?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I substitute multiple values in a cell using Excel’s Substitute function?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.