Formualizer Docs
ReferenceFunctionsText Functions

SUBSTITUTE

SUBSTITUTE: Replaces matching text within a string.

Summary

SUBSTITUTE can replace all occurrences or only a specific instance.

Remarks

  • Matching is case-sensitive.
  • If old_text is empty, the original text is returned unchanged.
  • With instance_num, only that 1-based occurrence is replaced.
  • Non-positive instance_num returns #VALUE!.

Examples

Replace all matches
Grid
CellValue
No inputs on Sheet1.
Formula
=
Result
Not evaluated yet.
Expected
x-b-x
Replace only second match
Grid
CellValue
No inputs on Sheet1.
Formula
=
Result
Not evaluated yet.
Expected
2024-01-FY24

FAQ

Is SUBSTITUTE case-sensitive?

Yes. It matches old_text with exact case and replaces either all or the requested instance.

Runtime metadata

Category

Text

Signature

SUBSTITUTE(arg1, arg2, ... argN: Any)

This function accepts a repeating argument pattern (min args: 3).

Arity

min 3, max variadic

Arguments

arg1

Any · Scalar

Caps

PURE

Source

crates/formualizer-eval/src/builtins/text/mid_sub_replace.rs

SEARCH

SEARCH: Returns the 1-based position of one text string inside another.

TEXT

Formats a value as text using a format pattern.

On this page

SummaryRemarksExamplesRelated functionsFAQIs SUBSTITUTE case-sensitive?Runtime metadata