Microsoft Office Tutorials and References
In Depth Information
3. Click a cell that contains the full string of which a portion is to be
4. Enter a comma ( ,).
6. Enter a comma ( ,).
7. Enter a number to tell the function how many characters are to be
8. Enter a comma ( ,).
This is the replacement text.
10. Type a ), and press Enter.
You can also use REPLACE to delete text from a string. Simply specify an
empty string ( “”) as the replacement text.
Use the SUBSTITUTE function when you don’t know the position in the target
string of the text to be replaced. Instead of telling the function the starting
position and number of characters (as you do with REPLACE), you just tell it
what string to look for and replace.
SUBSTITUTE takes three required arguments and a fourth optional argument:
✓ A reference to the cell that contains the target text string
✓ The string within the target string that is to be replaced
✓ The replacement text
✓ An optional number to tell the function which occurrence of the string
The fourth argument tells SUBSTITUTE which occurrence of the text to be
changed (the second argument) and actually replaced with the new text
(the third argument). The text to be replaced may appear more than once in
the target string. If you omit the fourth argument, then all occurrences are
replaced. This is the case in the first example in Table 16-2; all spaces are
replaced with commas. In the last example in Table 16-2, only the second
occurrence of the word two is changed to the word three.