ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel
  • »
  • Technology»
  • Computers & Software»
  • Computer Software»
  • Office Software Suites»
  • Microsoft Office

Tutorial - MS Excel - How to Substitute or Replace Text with other Text in Excel

Updated on May 11, 2017
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 your columnA, you can use the "Substitute" Formula or Function available in excel

3. Click on the first cell of columnB. Now 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 in 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 columnA, 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 5 months ago from Hyderabad, Andhra Pradesh

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

    • profile image

      Ronald Cortez 5 months ago

      Thanks for the great info.