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

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

## Steps

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

2. If you want to create data in a new column by replacing "* abc*" with "

*" in your*

**xyz***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 *

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.

## More MS Excel tutorials

- Tutorial - MS Excel - How to Concatenate in Excel

This is a step-by-step tutorial to learn how to concatenate data in excel - Tutorial - MS Excel - How to remove duplicates from an excel sheet

Step-by-step tutorial to remove duplicate values from a Microsoft excel worksheet. - Tutorial – MS Excel – How to add comments to a cell in an excel sheet

Step-by-step tutorial on how to show/Hide/Insert comments to a cell in an excel sheet - Tutorial – MS Excel – How to embed a file as an object in a Microsoft excel worksheet

Step-by-step tutorial to embed a file as an object in a Microsoft excel worksheet - Tutorial – MS Excel – How to Write Text Vertically or at an Angle in an Excel Sheet

This hub describes ways to be able to type text vertically or at an angle in a Microsoft Excel Sheet. - Tutorial – MS Excel – How to use VLookup in Microsoft excel

Step-by-step tutorial to use VLookup function to search for a list of values present in a table in Microsoft excel. - Tutorial – MS Excel – How to Print Comments Attached to a Cell

Ever wondered why the comments attached to cells are not printed when the excel sheet is printed? Step-by-step tutorial on how to print comments attached to cells, while printing a MS excel file. - Tutorial – MS Excel – How to highlight duplicate values in Microsoft excel without deleting them

Sometimes you may not want to delete the duplicate values but may want to highlight them anyway. Here's a step-by-step tutorial to highlight duplicate values in Microsoft excel without deleting them.

**© 2017 Petite Hubpages Fanatic**

## Comments

Thanks for the great info.