SUBSTITUTE in Calligra Sheets: Replace Matching Text

SUBSTITUTE replaces a particular text sequence with another string. With no fourth argument, =SUBSTITUTE("Cost Data";"Cost";"Sales") is expected to return Sales Data. With an occurrence index, it targets only that occurrence instead of every match.

=SUBSTITUTE(text;old_text;new_text;[instance_num])

• text — required source string or cell reference.
• old_text — required exact text sequence to replace; matching is case-sensitive in pinned 26.08.2 source.
• new_text — required replacement text. Specify "" to remove the matched text.
• instance_num — optional positive integer, counting matched occurrences from 1; if omitted, replace all matches. The implementation returns a #VALUE!-class error for 0 or negative occurrence values.
• Return — text. KDE registers 3–4 parameters. The empty old_text case returns the source text unchanged in the pinned implementation.

Aliases: none explicitly registered for this function in pinned 26.08.2 text module.

Version and evidence: The KDE Sheets function handbook and the pinned Calligra 26.08.2 text function code were checked on 2026-10-11. The code is release-registry/implementation evidence, not native execution proof. The 26.08.2 release announcement is dated 2026-10-08. For a particular installed package, confirm the version and enabled modules.

Formula entry and locale: Formulas begin with =. The English KDE reference uses semicolons to separate parameters, and all examples use English names and plain-ASCII text. Excel English-language reference pages typically show commas instead. Language packs, decimal notation and installed spreadsheet settings can affect formula entry. Paste the formula into a blank cell or use the function assistant to confirm the separator for your installation; do not assume a comma-formatted Excel formula always parses unchanged.

1. On a new blank sheet, enter A1 = Input text and B1 = Formula / result.
2. Enter each source text in A2 onward as shown in the table. For example, A2 = Cost Data.
3. Enter the shown formula in B2 onward. Use semicolons as shown in the handbook, or adapt the argument separator only if your local app requires it.
4. Compare the displayed result with the expected result. Text 0042 should not be coerced to number 42; (empty text) represents the empty string, not literal parentheses.

Validation label — independent calculation, not native execution: each expected result was recalculated using a separate Python string manipulation (ASCII-only for these inputs). The results are also consistent with the pinned implementation and relevant KDE unit-test expectations where available. A local Calligra Sheets 26.08.2 application was not available in this run; there are zero observed runtime outputs.

Normalize known labels without changing unrelated text

For A2 = Cost Data, =SUBSTITUTE(A2;"Cost";"Sales") gives Sales Data. Compare REPLACE, which would require the correct starting position and character count. SUBSTITUTE is more robust if the word moves to a different position in the input.

Replace one occurrence while leaving the rest untouched

For A6 = red, red, red, use:

=SUBSTITUTE(A6;"red";"blue";2)

Expected output: red, blue, red. This is useful when the second occurrence has a distinct meaning, but it is not a complete parser: extra repeated terms can change which occurrence is second.

Chain known substitutions

With A2 = Cost Data:

=SUBSTITUTE(SUBSTITUTE(A2;"Cost";"Sales");"Data";"Report")

Expected output: Sales Report. Nested SUBSTITUTE calls are straightforward for a handful of stable terms. For complex tokenization or replacements needing boundary awareness, a tested regular expression function or preprocessing workflow may be safer.

Remove a separator

With A9 = AB-12-CD, =SUBSTITUTE(A9;"-";"") is expected to return AB12CD. This removes all hyphens; a positional REPLACE can remove only one at a known index.

• The text did not change: verify old_text capitalization, spaces, and punctuation. The 26.08.2 implementation performs case-sensitive literal replacement.
• Wrong single match replaced: instance_num counts occurrences of old_text, not a character offset. Start counting at 1.
• Zero or negative occurrence: the pinned C++ implementation returns a #VALUE!-class error when instance_num ≤0.
• Occurrence beyond the number of matches: the implementation does not explicitly return the usual spreadsheet error before passing the position to Qt’s replacement operation. Because this edge case was not tested here, its result is unverified. Guard occurrence counts or avoid out-of-range values.
• Empty search string: pinned source returns the original text unchanged when old_text is empty. That is a source-based observation, not a native test on a packaged Calligra build.
• Missing replacement: pass "" to delete matching substrings. new_text itself is required, even when deleting.
• Variable delimiter counts: if a document sometimes includes extra repeated tokens, instance-specific replacement might alter a different logical field. Validate the data format first.

Microsoft Excel: English Excel uses SUBSTITUTE(text,old_text,new_text,[instance_num]), which agrees on the major 3–4-argument semantics. A supplied instance number chooses one occurrence; otherwise all occurrences are changed. Comma/semicolon entry depends on localization, and unusual Unicode/edge cases have not been cross-tested. Microsoft SUBSTITUTE reference.

Calligra REPLACE: REPLACE removes characters at a known position and requires four arguments. SUBSTITUTE instead searches for the content of old_text. Switching functions without changing the arguments is not safe.

• Calligra Sheets — function documentation
• Calligra 26.08.2 text function code
• 26.08.2 release announcement
• REPLACE
• FIND
• MID
• LEN

Verification status: Official documentation checked; example outcomes were independently reasoned or arithmetically checked where applicable. Not executed in Calligra Sheets.

• No genuine Calligra Sheets screenshots are included; this guide does not use mock application images.