Note:SUBSTITUTE
Description
SUBSTITUTE replaces a specified part of a text string with another text string.
Syntax
SUBSTITUTE ( text, old_text, new_text [, instance] )
|
Parameter |
Description |
|
text |
A text string that contains the text to replace. You can also specify a reference to a cell that contains text. |
|
old_text |
The text string to be replaced. |
|
new_text |
The replacement text. |
|
instance |
Specifies which instance of old_text shall be replaced, i.e, if instance = 2, the second occurance of old_text is replaced. If this argument is omitted, every instance of old_text is replaced. |
Examples
This function returns "Second Quarter Results":
SUBSTITUTE("First Quarter Results", "First", "Second")
This function returns "Shipment 45; Bin 52; No. 45":
SUBSTITUTE("Shipment 45, Bin 45, No. 45", 45, 52, 2)
Note:
If an Invalid formula syntax warning appears, check whether the comma is set as the list separator in your regional settings on the Windows Control Panel. If a different character is set as the list separator, use this character in the above examples in place of the comma. (Windows XP: Access the Windows Control Panel and select Regional and Language Options. On the Regional Options tab page, click Customize. On the Numbers tab page, check which character is the default list separator on your computer. Use this character in the above examples instead of the comma.
Also, see