Updated date:

MS Excel Tutorial: How to Substitute or Replace Text

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.

tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet

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").

tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet
tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet

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").

tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet
tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet

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

tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet
tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet

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.

tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet

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.

tutorial-ms-excel-how-to-convert-text-to-columns-in-a-microsoft-excelsheet

This article is accurate and true to the best of the author’s knowledge. Content is for informational or entertainment purposes only and does not substitute for personal counsel or professional advice in business, financial, legal, or technical matters.

© 2017 Petite Hubpages Fanatic

Comments

Petite Hubpages Fanatic (author) from Hyderabad, Andhra Pradesh on May 13, 2017:

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

Ronald Cortez on May 12, 2017:

Thanks for the great info.