MS Excel Tutorial: How to Substitute or Replace Text

Updated on January 3, 2018
nehanatu86 profile image

Neha is a software professional who specializes in ServiceNow Customization and Implementation. She likes writing tutorial articles.

Substitute Formula

Substitute(Text, old_text, new_text, [instance_num]).

Text: Existing Cell to pick up text from i.e., the reference to a cell containing text.

old_text: Text to be replaced.

new_text: New text to replace the old text.

instance_num: This is an optional argument where you can specify the occurrence of old_text that you want to substitute. For example, if instance_num = 1, then only the first occurrence of the old_text is replaced by the new_text. If this field is left blank, then every occurrence of old_text is replaced with the new_text.

Replace abc with xyz.
Replace abc with xyz.

Steps

1. Let us assume your sample data looks like this.

Sample data
Sample data

2. If you want to create data in a new column by replacing abc with xyz in column A, you can use the Substitute Formula that is available in Excel.

3. Click on the first cell of column B. From the top header menu, go to Formulas > Text > Substitute.

Formulas - Text - Substitute
Formulas - Text - Substitute

4. Select Substitute and the Formula appears in cell B1. The Formula Builder opens on the right hand-side.

Alternatively, you can type the formula directly into cell B1.

5. To substitute abc with xyz in the Formula Builder, click on the text box for Text and click on the first cell in column A so that A1 appears in the Text field of the Formula Builder.

Alternatively, you can type A1 as the first argument in your formula directly: =Substitute("A1").

6. In the Formula Builder, click on the text box for old_text and type abc.

Alternatively, you can type abc as the second argument in your formula directly: =Substitute("A1","abc").

7. In the Formula Builder, click on the text box for new_text and type xyz.

Alternatively, you can type xyz as the third argument in your formula directly: =Substitute(A1,abc,xyz).

8. Click on the Done button in the Formula Builder to show the result.

Alternatively, you can hit Enter after you finish typing the formula directly in the cell to show the result.

9. To do the same for all the rows, take the cursor to the bottom right corner of the cell B1 and hold & drag till the end of the data to copy the formula to the other cells.

© 2017 Petite Hubpages Fanatic

Comments

    0 of 8192 characters used
    Post Comment

    • nehanatu86 profile image
      Author

      Petite Hubpages Fanatic 11 months ago from Hyderabad, Andhra Pradesh

      I'm glad you found it helpful Ronald. Thanks for reading!

    • profile image

      Ronald Cortez 11 months ago

      Thanks for the great info.

    working