Tutorial - MS Excel - How to Substitute or Replace Text with other Text in Excel
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.
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 "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
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:
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:
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:
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