SUBSTITUTE(): Replace text with another (substitution)

The SUBSTITUTE() function replaces one or more occurrences of a string with another within a text, and returns a text string. It is useful for correcting or standardizing values.

The output type format of the result is configured in the output field options of the formula editor: see Formula Column.

Syntax

SUBSTITUTE(original_text, old_text, new_text, instance_number)

Referencing parameters in TimeTonic: $field_name for a field (link, linked table column, number, selection, formula, text); "free text" in quotes; no quotes for numbers.

Parameters

# Parameter Description

1 original_text The original string where the replacement will be made.

2 old_text The text to find and replace.

3 new_text The value used to replace old_text.

4 instance_number The occurrence number to replace; if omitted, all occurrences are replaced.

Examples

Replace a file extension

"TT-2026-01" becomes "TM-2026-01".

SUBSTITUTE("TT-2026-01", "TT", "TM")

Replace only the second occurrence of "a" with "o"

"banane" becomes "bonane".

SUBSTITUTE("banane", "a", "o", 2)

Replace all occurrences (without instance number)

"banane" becomes "baoaoe": without an instance number, all occurrences of "n" are replaced by "o".

SUBSTITUTE("banane", "n", "o")

SUBSTITUTE() is case-sensitive. If instance_number is greater than the number of occurrences, the original text is returned.

Business use case: SUBSTITUTE() is especially 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().

Learn more

REPLACE

Replace a text segment by position and length.

Read more →

Or continue with

Formula Glossary

All available operators and functions, categorized.