Searches through a main string value for a specified string, and replaces the numbered instance of that found value with the replacement text specified.
Substitute([TextToSearchIn], [OriginalText], [ReplacementText], [InstanceNumber], [IgnoreCase])
Where:
TextToSearchIn is the main string value that the specified instance is to be replaced in.
OriginalText is the string instance to be found and replaced within the main string value.
ReplacementText is the string that the user would like to replace the OriginalText value with.
InstanceNumber is the occurrence number of the OriginalText string found that is to be replaced. If this is left blank, then the function will replace all instances it finds. If no occurrence is found, or if there are less instances than the number specified, then the text will remain unchanged.
IgnoreCase set to TRUE will substitute all instances of the text, ignoring upper case and lower case.
Rule | Meaning |
|---|---|
| Substitute("My name is Joe", "Joe", "Fred", 1,TRUE) | The function searches the string "My name is Joe" and finds the first instance (defined by the numerical value of 1 as the final parameter) of the value "Joe", and replaces it with the string "Fred". The result in this case would be: "My name is Fred". |
| Substitute("My name is Joe and his name is Joe", "Joe", "Fred", 2, TRUE) | The function searches the string "My name is Joe and his name is Joe" and finds the second instance (defined by the numerical value of 2 as the final parameter) of the value "Joe", and replaces it with the string "Fred". The result in this case would be: "My name is Joe and his name is Fred". |