How to Use Excel's SUBSTITUTE Function

Excel's SUBSTITUTE function is useful if you have a lot of cells that have the same data format but different values. With the SUBSTITUTE function, you can enter information once, then write a formula displays a variation of that cell throughout the spreadsheet.

Instructions

Difficulty: Easy

1. Choose the cell that you want to enter the formula into. Click it with your mouse. Click inside the function (fx) box, just below the Excel menu.
2. Type in the function in this format: "=SUBSTITUTE(text, "old_text", "new_text", instance_num)." "Text" is the cell that contains the text you want to subsitute. "Old_text" is the text to be replaced and "new_text" is the text to replace it with. "Instance_num" is the instance of the text you want to replace.
3. Create an example to learn how the substitute function works. Click on the top gray cell so that the whole spreadsheet is highlighted. Click "format," "cells" from the menu. Select "text."
4. Enter "November 2, 2008" in A1.
5. Click on cell B1. Go to the function box, and type "=SUBSTITUTE (A1, "2", "4", 1). Hit enter. The text in B1 will now read "November 4, 2008." Then edit the formula and take out the "1." All instances of "2" will be changed to "4."
6. Go to cell B2. In the function box, type "=SUBSTITUTE (A1, "8", "12"). Hit enter. The year now read "2012." In this case you don't need the "instance_num," since there is only one "8" in the cell.
7. Get help and other examples of the substitute function by clicking "help" on Excel's menu. In the "keywords" box, type "substitute worksheet function."

Who Can Help:

• Find Computer, Electrical and Telephone Services Here. Compare Up to 4 Quotes, Free Service & No Obligation.
• Find Electrical Services Here. Compare Up to 4 Quotes, Free Service & No Obligation.