Table of Contents
Google Sheets is a powerful and user-friendly spreadsheet program that allows users to easily manage and organize data. One useful feature of Google Sheets is the ability to substitute multiple values at once, which can save time and effort when working with large sets of data. This feature allows users to replace one or more values in a particular range or cell with a different value, without having to manually edit each individual cell. By using the “Find and Replace” function in Google Sheets, users can quickly and efficiently substitute multiple values at once, making data management and manipulation much more efficient.
Google Sheets: Substitute Multiple Values
You can use the following formula to substitute multiple values in a cell in Google Sheets:
=SUBSTITUTE(SUBSTITUTE(A1,"oldtext1","newtext1"),"oldtext2","newtext2")
This particular formula substitutes 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 examples show how to use this formula in practice.
Example 1: Substitute Two Values in Google Sheets
Suppose we have the following list of basketball positions in Google Sheets:
We can use the following formula to substitute “Guard” with “Gd” and “Forward” with “Fd”:
=SUBSTITUTE(SUBSTITUTE(A2,"Guard","Gd"),"Forward","Fd")
The following screenshot shows how to use this formula in practice:
Notice that the words “Guard” and “Forward” were replaced in each cell with the substitutions that we specified in the formula.
Example 2: Substitute Three Values in Google Sheets
Suppose we have the following list of basketball positions in Google Sheets:
We can use the following formula to substitute “Point” with “Pt”, “Shooting” with “St” and “Small” with “Sm”:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Point","Pt"),"Shooting","St"),"Small","Sm")
Notice that each of the substitutions have been made.
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets:
Cite this article
stats writer (2024). How can I use Google Sheets to substitute multiple values at once?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-substitute-multiple-values-at-once/
stats writer. "How can I use Google Sheets to substitute multiple values at once?." PSYCHOLOGICAL SCALES, 1 Jul. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-substitute-multiple-values-at-once/.
stats writer. "How can I use Google Sheets to substitute multiple values at once?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-substitute-multiple-values-at-once/.
stats writer (2024) 'How can I use Google Sheets to substitute multiple values at once?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-google-sheets-to-substitute-multiple-values-at-once/.
[1] stats writer, "How can I use Google Sheets to substitute multiple values at once?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, July, 2024.
stats writer. How can I use Google Sheets to substitute multiple values at once?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.