SUBSTITUTE Function (LibreOffice Calc)

• Replaces matching text within a string
• Can replace all occurrences or a specific occurrence
• Case-sensitive
• Does not support wildcards
• Works with text, numbers (converted to text), and formulas

It is designed to be semantic, predictable, and ideal for cleaning and transforming text.

SUBSTITUTE(text; old_text; new_text; [instance])

Arguments

• text:
  The original text string.

• old_text:
  The text to replace.

• new_text:
  The replacement text.

• instance: (optional)
  Which occurrence to replace:

  • Omitted → replace all occurrences
  • 1 → replace first occurrence
  • 2 → replace second occurrence
  • etc.

Replace all occurrences

=SUBSTITUTE("A-B-C"; "-"; "_")

Returns "A_B_C".

Replace only the first occurrence

=SUBSTITUTE("A-B-C"; "-"; "_"; 1)

Returns "A_B-C".

Replace only the second occurrence

=SUBSTITUTE("A-B-C"; "-"; "_"; 2)

Returns "A-B_C".

Replace text in a cell

=SUBSTITUTE(A1; "apple"; "orange")

Replace text in a number (converted to text)

=SUBSTITUTE(2024; "20"; "99")

Returns "9924".

Remove all spaces

=SUBSTITUTE(A1; " "; "")

Replace only the last occurrence

=SUBSTITUTE(A1; "-"; "☼"; LEN(A1)-LEN(SUBSTITUTE(A1; "-"; "")))

Replace Nth occurrence dynamically

=SUBSTITUTE(A1; "-"; "_"; B1)

Where B1 contains the occurrence number.

Normalize inconsistent delimiters

=SUBSTITUTE(SUBSTITUTE(A1; "/"; "-"); "_"; "-")

Replace text before extraction

=LEFT(SUBSTITUTE(A1; "_"; "-"); FIND("-"; SUBSTITUTE(A1; "_"; "-")) - 1)

Remove non-breaking spaces

=SUBSTITUTE(A1; CHAR(160); " ")

Replace multiple patterns using nesting

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1; "x"; ""); "y"; ""); "z"; "")

Clean imported data

=SUBSTITUTE(TRIM(A1); CHAR(9); "")

(Removes tabs.)

Replace text inside structured codes

=SUBSTITUTE(A1; MID(A1; 4; 3); "XXX")

Replace only if text exists

=IF(ISERROR(FIND("old"; A1)); A1; SUBSTITUTE(A1; "old"; "new"))

SUBSTITUTE is case-sensitive

=SUBSTITUTE("Apple"; "a"; "X") → "Apple"  
=SUBSTITUTE("Apple"; "A"; "X") → "Xpple"

SUBSTITUTE does not support wildcards

Use SEARCH for wildcard matching.

instance < 1 → Err:502

=SUBSTITUTE("ABC"; "A"; "X"; 0)

instance > number of occurrences → returns original text

=SUBSTITUTE("A-B"; "-"; "_"; 5) → "A-B"

text is a number → converted to text

=SUBSTITUTE(12345; "23"; "XX") → "1XX45"

text is empty

=SUBSTITUTE(""); "A"; "B") → ""

old_text empty → Err:502

LibreOffice requires old_text to be non-empty.

new_text may be empty

=SUBSTITUTE("ABC"; "B"; "") → "AC"

text is an error → error propagates

=SUBSTITUTE(#N/A; "A"; "B") → #N/A

• Use SUBSTITUTE for pattern-based replacement
• Use REPLACE for positional replacement
• Normalize text with TRIM and CLEAN before substitution
• Use nested SUBSTITUTE calls for multi-pattern cleaning
• Use FIND/SEARCH to target specific occurrences
• Use LEN to calculate dynamic instance numbers

• Use REPLACE for positional editing
• Use LEFT, RIGHT, MID for extraction
• Use FIND and SEARCH to locate text
• Use LEN to calculate dynamic replacement ranges
• Use TEXTJOIN or CONCAT for reconstruction

By mastering SUBSTITUTE and its companion functions, you can build powerful, flexible, and highly controlled text‑processing workflows in LibreOffice Calc.