Replaces existing text with new text in a text string.
Unlike the Excel SUBSTITUTE function, this function does not limit the text argument values to 255 characters,
and also allows for specifying whether a match is based on a case-sensitive or a case-insensitive comparison.
Deprecated to eliminate ambiguity between the Excel Substitute function and the Dodeca Substitute function. Use the Dodeca Replace function is place of the Dodeca Substitute function.
@Substitute(<Text>, <OldText>, <NewText>, [<CaseSensitive>], [<InstanceNumber>])
| Argument Name | Description |
|---|---|
| CaseSensitive | Optional (default is true); Controls whether a case-sensitive comparison is used to detect a match. |
| InstanceNumber | Optional (default is 0, which indicates that all occurrences are replaced); The occurrence of OldText to replace with NewText, starting at 1 to indicate the first occurrence, 2 for the second, etc. |
| NewText | The text to replace the OldText with. |
| OldText | The text to replace. |
| Text | The text in which the occurrence(s) of OldText are replaced with NewText. |
function example
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Text | OldText | NewText | CaseSensitive | InstanceNumber | Eval | Comments |
| 2 | the cow had a cow | cow | pig | No instance number added results in all instances being substituted | |||
| 3 | the cow had a cow | cow | pig | 1.0 | Instance number of 1 will result in the first occurence being substituted | ||
| 4 | the cow had a cow | cow | pig | 2.0 | Instance number of 2 will result in the second occurence being substituted |
| Procedure | ||||||
|---|---|---|---|---|---|---|
| Method | Overload | ErrorHandlingMode | ||||
| Argument | Value | Comment | ||||
| OnAfterBuild | ||||||
| SetEntry | Text | |||||
| Address | ContainsRange | |||||
| CellByCell | True | |||||
| Entry | @Substitute(@ValueText(A@CRow()), @ValueText(B@CRow()), @ValueText(C@CRow()), @ValueText(D@CRow()), @ValueText(E@CRow())) | |||||