Microsoft Office Tutorials and References
In Depth Information
Changing Text
Table 16-2
Applying the SUBSTITUTE Function
Example
Returned String
Comment
=SUBSTITUTE(“apple
banana cherry fig”,
“ “,”,”)
apple,banana,
cherry,fig
All spaces are
replaced with
commas.
=SUBSTITUTE(“apple
banana cherry fig”, “
“,”,”,1)
apple,banana
cherry fig
The first space is
replaced with a
comma. The other
spaces remain as
they are.
=SUBSTITUTE(“apple
banana cherry fig”, “
“,”,”,3)
apple banana
cherry,fig
The third space
is replaced with a
comma. The other
spaces remain as
they are.
=SUBSTITUTE(“There
are two cats and two
birds.”,”two”,”three”)
There are
three cats and
three birds.
Both
occurrences of two
are replaced with
three .
=SUBSTITUTE(“There are
two cats and two birds.
”,”two”,”three”,2)
There are two
cats and three
birds.
Only the second
occurrence of
two is replaced
with three .
Try it yourself! Here is what you do:
1. Position the cursor in the cell where you want the result to appear.
2. Enter =SUBSTITUTE( to begin the function entry.
3.Clickacellthatcontainstext,orenteritsaddress.
This is the full string of which a portion is to be replaced.
4. Enter a comma ( ,).
5.Clickacellthatcontainstext,orentertextenclosedindouble
quotationmarks.
This is the portion of text that is to be replaced.
6. Enter a comma ( ,).
7.Clickacellthatcontainstext,orentertextenclosedindouble
quotationmarks.
 
Search JabSto ::




Custom Search