Description
Substitutes one or all occurrences of a Text within a Text with a new Text.
Syntax
SUBSTITUTE(Text to Search, Text to Replace, Text to Replace With, [, Occurrence Number to Replace])
Arguments
| Argument | Type | Dimensions | Description |
|---|---|---|---|
| Text to Search (required) | Text | Any Dimensions | Text to search in. |
| Text to Replace (required) | Text | No Dimension or Dimensions of Text to Search | Text within Text to Search to replace. |
| Text to Replace With (required) | Text | No Dimension or Dimensions of Text to Search | Text to substitute all found occurrences of Text to Replace with. |
| Occurrence Number to Replace (optional) | Integer | No Dimension or Dimensions of Text to Search | Number of the occurrence found to substitute. By default, all found occurrences are replaced. If specified, only the given occurrence is replaced. |
Returns
| Type | Dimensions |
|---|---|
| Text | Dimensions of Text to Search |
The original Text to Search with all Text to Replace substituted by Text to Replace With.
BLANK if Occurrence Number to Replace is a negative integer.
Examples
| Formula | Result | Description |
|---|---|---|
SUBSTITUTE(“aaa”, “a”, “b”) | “bbb” | All occurrences of “a” are replaced with “b”. |
SUBSTITUTE(“aaa”, “a”, “b”, 2) | “aba” | Only the second occurrence of “a” is replaced by “b”. |
SUBSTITUTE(“Fimamcial Analyst” “Fimamcial”, “Financial”) | “Financial Analyst" | The Text “Fimamcial” is replaced with “Financial” |
SUBSTITUTE(“abc”, “A", “d”) | “abc” | “A” is not present in “abc”. Nothing is replaced. |
SUBSTITUTE(“abc”, “a”, “e”, -1) | BLANK | The Occurrence Number to Replace, -1, is invalid. |
See also
Excel: SUBSTITUTE
Related articles: FIND, CONTAINS
More of a hands-on learner?
Talk to your Customer Success Manager about downloading the Functions and Modifiers in Pigment Application into your workspace. It includes examples of every formula and modifier in Pigment!
