# 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.

---

## Discussion

## New Comments

No comments yet. Be the first one!