How to Replace Multiple Values at Once in MS Excel

What is SUBSTITUTE Function..?

The SUBSTITUTE formula is a function in Microsoft Excel that is used to replace a particular text (old_text) with other text (new_text) in a text or value. This formula is very useful when you need to replace specific sentences, words, or characters within text.

Formula - 

=SUBSTITUTE(text, old_text, new_text, [instance_num])

1. text: This is the original text string to be modified.
2. old_text: This is the text that needs to be replaced.
3. new_text: This is the text that will be placed in place of old_text.
4. instance_num (optional): If specified, specifies which occurrence of old_text to replace. If omitted, the formula replaces all occurrences.

Table of Contents

• What is SUBSTITUTE Function..?
• Example for SUBSTITUTE Function
• Step by Step Guide for SUBSTITUTE Function
• Watch Video Tutorial for SUBSTITUTE formula
• Conclusion

Example for SUBSTITUTE Function

Suppose you have a data set in which you need to replace some placeholders in a text string with actual values. 

Here is an example data set:

Our objective is to replace "ABC", "XXX", and "YYY" in the sentence given in the Sentence Sample column with the values ​​of Name, Fruit 1, and Fruit 2 columns respectively.

Step by Step Guide for SUBSTITUTE Function

Prepare Data as shown in above image 

• First, make sure your data is setup correctly in Excel:
• Column A: Name
• Column B: Fruit 1
• Column C: Fruit 2
• Column D: Sentence Sample
• Column E: Final output (where the result will appear)

Create a SUBSTITUTE formula for multiple replacement 

To replace multiple values ​​in a single text string, you can nest multiple SUBSTITUTE formulas. Here's the formula for our example:

Formula - 

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2, "ABC", A2), "XXX", B2), "YYY", C2)

1. FIRST SUBSTITUTE: Replaces "ABC" with the value of A2.
2. SECOND SUBSTITUTE: Replaces "XXX" with the value in B2 from the result of the first SUBSTITUTE.
3. THIRD SUBSTITUTE: Replaces "YYY" with the value in C2 from the result of the previous two steps.

Apply SUBSTITUTE formula

First, enter the formula in the first cell of the Final Output column (E2) where you want to see the results.

Then, drag the fill handle (the small square in the lower right corner of the cell) down to apply the formula to other rows.

Check Results

After you apply the formula, make sure that the text string in the Final Output column is updated correctly with all placeholders as it should be based on the data in the A, B, and C columns.

Conclusion

Using SUBSTITUTE formulas for multiple values in Excel is an effective way to convert multiple values ​​into a text string at once. This technique is extremely useful for data cleaning and formatting tasks where you need to update text dynamically based on cell values.

By following these steps, you can easily manage and transform your data in Excel, ensuring that your text strings are updated as needed.