How to replace multiple texts with the SUBSTITUTE function.(Microsoft Excel)
The SUBSTITUTE function for replacing text only supports replacing one text, not multiple texts.
Here is how to replace multiple texts without using macros.
Procedure.
The SUBSTITUTE function returns the resulting replaced text.
Since the substituted text can then be used as an argument to another SUBSTITUTE function, multiple text substitutions are possible by nesting SUBSTITUTE functions.
Example of entering formulas in multiple cells.
We will replace "A:B:C:D" with one character at a time.
First, replace "A" with "w".
This can be repeated to replace any number of texts.
Example of entering a formula in a single cell.
Conventional Method.
Multiple SUBSTITUTE functions are valid for a single cell.
However, the more functions there are, the more difficult the formulas become to read.
This makes it easier to make mistakes when updating formulas, so it is not recommended.
Unless it is absolutely necessary to keep them in one cell
It is recommended to arrange them in multiple columns or rows and hide the cells in the middle.
How to use the LAMBDA function.
If Excel reflects the LAMBDA function, the
This can be accomplished without layering the SUBSTITUTE function.
Use REDUCE, LAMBDA, and OFFSET for the formulas.
=REDUCE(Text,Old_text_cell_range,LAMBDA(r,s,SUBSTITUTE(r,s,OFFSET(s,0,1))))
New_text is placed to the right of Old_text and only the cell range of Old_text and Text are specified in the formula. Text in the formula.
---