The SUBSTITUTE perform in Google Sheets helps you to discover a particular textual content worth in a cell and substitute it with one other worth. If you should substitute a number of values in a string with completely different values, you should nest a number of SUBSTITUTE capabilities, which may be cumbersome.
Google Sheets affords a built-in SUBSTITUTE
perform that may discover a particular textual content in a cell and substitute it with one other worth. You should utilize for instance =SUBSTITUTE("My favourite coloration is purple", "purple", "blue")
to exchange the textual content purple
within the string with blue
. The SUBSTITUTE
The perform is case delicate and replaces all occurrences of the search textual content.
Substitute a number of values with SUBSTITUTE
Now contemplate a state of affairs the place you should substitute a number of values in a string with completely different values. For instance, you probably have a template set like My title is title and I work at firm
and also you wish to substitute title
with actual title and firm
with the corporate title.
The SUBSTITUTE
perform isn’t helpful right here as a result of it may well solely substitute one worth at a time, however you need to use nested SUBSTITUTE
capabilities to exchange a number of values in a single cell. There can be one SUBSTITUTE
perform for every worth you wish to substitute.
Nested SUBSTITUTE capabilities
=SUBSTITUTE(
SUBSTITUTE(A1,"title","Amit"),
"firm","Digital Inspiration")
A number of substitute capabilities for Google Sheets
The nested method works, however the system can turn out to be lengthy and complicated if you should substitute a number of values in a single cell. This is an easier method that makes use of Google Apps Script to create a customized perform that may substitute a number of values in a single name.
=MULTI_SUBSTITUTE(A1, "replace_1", "value_1", "replace_2", "value_2", ... "replace_n", "value_n")
The perform takes the enter string as its first argument after which pairs search and substitute values. Every pair has two values: the primary worth is the search textual content and the second worth is the substitute textual content. The perform replaces all occurrences of the search textual content within the enter string with the corresponding substitute textual content.
Open your Google Sheet, go to Extensions > Apps Script and paste the next code into the script editor. Save the script and now you can use the MULTI_SUBSTITUTE
perform in your Google Sheet to exchange a number of values in a single cell.
perform MULTI_SUBSTITUTE(textual content, ...opts)
for (let i = 0; i < opts.size; i += 2) '');
return textual content;
This practice perform makes use of common expressions to exchange all occurrences of the search worth within the enter string. The i
flag within the common expression makes the search case-insensitive, in contrast to the built-in expression SUBSTITUTE
perform.
You may as well use the a number of substitutions function to generate pre-populated hyperlinks for Google Types.