SUBSTITUTE replaces an exact piece of text with another piece of text. If A5 contains INV|2026|0412, then =SUBSTITUTE(A5,"|","-") is expected to return INV-2026-0412. Supply the optional occurrence number to replace just one occurrence instead of all of them. See the current official SUBSTITUTE reference.

This tutorial covers one canonical SUBSTITUTE function, not a separate page for localized names, byte-count variants or aliases. Use the formula when the goal is to produce a changed text value without editing the original source cell. Keep raw source values in column A and transformed results in another column. For exact matching against full codes or full rows, consider a lookup or validation workflow instead of mistaking a substring/position operation for a complete data-quality check.

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

Official interpretation: The ONLYOFFICE SUBSTITUTE reference gives the four arguments and specifically defines omitted instance_num as all occurrences. It does not document regular expressions; use a separate REGEXREPLACE reference rather than treating old_text as a regex. The checked source does not conclusively describe case sensitivity or every invalid occurrence-number error, so native tests are reserved for those cases.

Square brackets are documentation notation for optional parameters; do not type them into a real formula. The = sign is required for a formula in a sheet. The function name is English because these examples follow English ONLYOFFICE help.

The current English-language ONLYOFFICE Spreadsheet Editor function index and the linked function page document this function as part of ONLYOFFICE Docs Spreadsheet Editor. Both references were checked 2026-10-11 (UTC). The function help is not scoped to a numbered product build: first-supported version = UNKNOWN from this evidence. The index is not a promise that every historical Docs edition, Desktop Editors version, embedded editor or mobile app accepts the formula. No native edition/platform has been opened during this research run.

The Docs release history and the separate Desktop Editors history were consulted as version context, not as proof of a particular introduction version for this function. A Desktop release number must not be silently treated as the Docs server version. If supporting older releases, test the exact installed build and save the result before changing the wording.

For an English formula, use = plus the uppercase function name, parentheses, commas between arguments, and straight double quotes around literal strings, as illustrated in the official function-insertion guide. ONLYOFFICE lets the user choose a Formula Language and regional settings (official Advanced Settings); localized names or argument separators should be checked in the installed editor’s Function Arguments panel. Do not assume a delimiter or English spelling is universal in every locale. The examples below use English names, comma separators, 1-based character positions and plain ASCII strings; avoid date/numeric autocorrection by formatting source cells as text where appropriate.

Open a blank ONLYOFFICE workbook. In the first sheet, enter the exact raw text below into A1:A9. The examples treat these as text, not formula expressions. Keep all results in B or C so that the original values are preserved for auditing.

Important for A5: type the literal value INV|2026|0412 using normal vertical-bar characters. The | codes in the Markdown table only keep its columns aligned; do not type those codes into the spreadsheet. In A1 type Source text as a normal header. Do not copy backticks into the worksheet.

1. Select a result cell outside column A; enter each formula shown below, starting with =.
2. Press Enter; compare it to the expected result listed for the exact row. Expected results are calculations/logic checks, not observed output from ONLYOFFICE.
3. If a result differs, check input capitalization, hidden spaces, typographic quotation marks, cell format and locale. Re-enter the formula using Formula → Function or the Function Arguments helper.
4. Use Formula → Show Formulas to distinguish formulas from literal text and, if appropriate, recalculate using the Formula tab (both operations are described in the official editor instructions).

Use the input cells exactly as shown. Type each formula into the indicated output cell (B2:B7); leaving the originals in column A avoids accidental overwrites.

How the position/occurrence reasoning was verified: these examples use literal ASCII text and independent character/substring operations. For an official expected error, such as #VALUE! on a no-match result, that classification comes from the directly cited ONLYOFFICE function documentation, not from executing the error in ONLYOFFICE. A result with a different case, extra space or character changes the test input and therefore requires a fresh expectation.

Standardize two separate pieces of a supplier reference

A5 is INV|2026|0412. This chained formula changes the separators and the prefix:

=SUBSTITUTE(SUBSTITUTE(A5,"|","-"),"INV","ORD")

Expected ORD-2026-0412. The inner call produces INV-2026-0412; the outer call changes the INV substring to ORD. Each invocation is documented, but the full expression is not native-executed. This is a text transformation, not a unique identifier validity check.

Target a domain segment without changing the username

For A7 (sales@Example.com), =SUBSTITUTE(A7,"Example.com","example.ca") has the independently calculated expected output sales@example.ca. This works for that exact literal sample; using SUBSTITUTE alone is not a robust email parser. Preserve the original contact field and validate real addresses separately.

1. The fourth parameter is an occurrence count, not a character index. To change characters by position, use REPLACE instead.
2. Leaving instance_num out changes all matching occurrences according to the official ONLYOFFICE documentation; setting it to 2 targets the second literal match.
3. A difference in capitalization can alter whether text matches in many spreadsheet engines; the inspected ONLYOFFICE help does not specifically promise every case-sensitive behavior. Treat mixed-case behaviour as PENDING_NATIVE_TEST.
4. Do not assume wildcard or regex syntax inside old_text works. The official SUBSTITUTE page describes replacement of a text substring, not pattern matching.
5. Empty old_text, invalid/noninteger instance_num, and typed numeric coercion need application-specific verification before documenting exact errors. Avoid telling readers a particular error code without evidence.

Unresolved behaviour: Literal-match mixed-case behaviour, empty old_text, noninteger occurrence, Unicode normalization and unsupported regex syntax not experimentally established. Testing with simple ASCII strings is intentional: no assumption is made that byte-based variants, localized spellings, or Unicode indexing behave identically.

If the editor still disagrees with the expected ASCII outputs, first verify the current formula by selecting the result cell, then confirm each literal in A2:A9 matches the practice table. A copied formula may move references unintentionally; use anchored references only when you need them. Do not alter production data to make a tutorial example appear to pass.

Microsoft Excel uses the same four argument roles and distinguishes SUBSTITUTE (text matching) from REPLACE (position replacement). That is explicitly documented by Microsoft SUBSTITUTE and the ONLYOFFICE reference. Excel-specific odd cases, localized text coercion and non-ASCII matching cannot be assumed identical without paired tests.

The compatibility statement above is limited to documented function roles and English-language syntax. No Microsoft Excel, LibreOffice, Google Sheets, Apple Numbers or ONLYOFFICE comparison formula was actually run for this batch. Avoid claiming that the same error codes, Unicode positions, wildcard language, formula separators and byte-based variants are identical across products.

Only an existing source-confirmed URL is linked as a local site guide. Other named related functions are review-stage references, not claims that their pages are published or currently linked on the live website.

• ONLYOFFICE Spreadsheet Editor — function documentation
• separate REGEXREPLACE reference
• ONLYOFFICE Spreadsheet Editor function index
• Docs release history
• Desktop Editors history
• official Advanced Settings

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

• No genuine ONLYOFFICE Spreadsheet Editor screenshots are included; this guide does not use mock application images.