Browse

SUBSTITUTE Function (LibreOffice Calc)

Text Intermediate LibreOffice Calc LibreOffice 3.0

The SUBSTITUTE function in LibreOffice Calc replaces occurrences of specific text within a string. It is essential for data cleaning, normalization, pattern-based replacement, and transforming structured text.

The **SUBSTITUTE** function replaces occurrences of specific text within a string in LibreOffice Calc. Unlike REPLACE, which works by position, SUBSTITUTE works by **matching text**, making it ideal for data cleaning, normalization, and pattern-based transformations.

This guide provides syntax, examples, edge cases, and best practices, making it one of the most complete SUBSTITUTE references available for LibreOffice Calc.

What the SUBSTITUTE Function Does

  • 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.

Syntax

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.

Basic Examples

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".

Advanced Examples

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"))

Edge Cases and Behavior Details

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

Common Errors and Fixes

Err:502 — Invalid argument

Occurs when:

  • instance < 1
  • old_text is empty
  • instance is non-numeric

SUBSTITUTE does nothing

Cause:

  • Case mismatch
  • Hidden characters
  • Non-breaking spaces

Fix:
Normalize with TRIM, CLEAN, or SUBSTITUTE(…; CHAR(160); " “).

SUBSTITUTE replaces too much

Cause:

  • Missing instance argument
  • Overlapping patterns

Best Practices

  • 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
SUBSTITUTE is your semantic replacement engine — perfect for cleaning, normalizing, and transforming text based on matching content rather than position.
  • 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.

Reproducible example (tested in LibreOffice Calc)

This standalone example was calculated in LibreOffice Calc 25.2.3.2 and matched the expected value. It is a practical cross-check of one usage, not proof that every example elsewhere on this page is correct.

=SUBSTITUTE("blue car";"blue";"red")

Expected result: red car.

Replace matching text wherever it appears.

If the result differs, check the referenced cells, argument separators and number formatting. For more information, consult the LibreOffice Calc official help.