The SUBSTITUTE() function replaces one or more occurrences of a string with another within a text. It is useful for correcting or standardizing values.
Text function name:
SUBSTITUTE()
Syntax
SUBSTITUTE(original_text, old_text, new_text, instance_number)ℹ Calling function parameters in TimeTonic:
- $field_name = field (link, linked table column, number, selection, formula, text...).
- "free text" = free text to be added between quotation marks.
- do not use quotation marks for numbers.
Parameters
| Parameter | Description |
|---|---|
original_text |
The original string where the replacement will be performed. |
old_text |
The text to search for and replace. |
new_text |
The value used to replace old_text. |
instance_number |
The number of the occurrence to replace; if omitted, all occurrences are replaced. |
Example
To replace the extension of a file:
SUBSTITUTE("TT-2026-01", "TT", "TM")Result: "TM-2026-01"
To replace only the second occurrence of “a” with “o”:
SUBSTITUTE("banana", "a", "o", 2)Result: "baonane"
Notes
SUBSTITUTE()is case sensitive.If
instance_numberis greater than the number of occurrences, the original text is returned.
Return type
The function returns a text string.
The output type format of the result can be configured in the output field options of the formula editor. For more details, see Output format options in the Formula column article.
Business use case
SUBSTITUTE() is particularly useful for cleaning data entered by users or imported from external sources (API, CSV, forms).
Best practice
Before any automation or comparison, it is recommended to normalize data (spaces, special characters, separators) using SUBSTITUTE(), TRIM(), or UPPER().