Browse

SUBSTITUTE in SoftMaker PlanMaker 2026: Replace Matching Text

Text Beginner SoftMaker PlanMaker 2026

Learn to replace all or one occurrence of text in PlanMaker with SUBSTITUTE, including case sensitivity, sample cell data, and troubleshooting.

Verification status: Official documentation referenced; example outcomes independently reasoned or arithmetically checked where stated. Not executed in SoftMaker PlanMaker 2026. How formula examples are checked

SUBSTITUTE in SoftMaker PlanMaker: replace matching text

When SUBSTITUTE is useful

Use SUBSTITUTE when you know the literal characters that should change but not necessarily their position. Examples include standardizing hyphens in exported product IDs, updating labels in an inventory list, or replacing only the second appearance of a repeated token. Unlike REPLACE, which starts at a numbered character position, SUBSTITUTE finds the specified text. It is case-sensitive in the official PlanMaker manual. Source.

Official syntax and exact arguments

=SUBSTITUTE(Text, OldText, NewText [, n])
Argument Required? What it does
Text Yes Text value, literal or cell reference, to inspect.
OldText Yes Exact character sequence to find. The comparison is case-sensitive.
NewText Yes Characters to insert in place of a matching occurrence. Can be an empty string "" to remove a matched token (test in your installation).
n No The occurrence number to change. When omitted, every matching occurrence is replaced; 1 and 2 choose the first and second respectively.

The bracket notation [, n] describes an optional argument, not literal square brackets to type. Official help does not give a complete table of invalid n values, empty-needle behavior or maximum text length; do not assume an exact error code for those edge cases without a native test. Source: PlanMaker 2026 SUBSTITUTE.

Language and separators: These examples use English function names and comma-separated arguments, consistent with the English versioned help. Formula naming and separators can vary by installed language/region; localized input was not tested. If the installed parser rejects a comma, use Formula → Function to enter the arguments in your installation rather than guessing a localized separator. See entering formulas.

Reproduce the example in a new worksheet

Type this text exactly in column A (these are strings, not formulas):

Cell Text to enter
A1 Product ID
A2 CA-2026-RED-RED
A3 EU-009-RED
A4 CA-2026-red
A5 CA--2026--RED

Enter these formulas in empty cells outside column A:

Cell Formula to enter Expected text result
B2 =SUBSTITUTE(A2,"RED","BLUE") CA-2026-BLUE-BLUE
C2 =SUBSTITUTE(A2,"RED","BLUE",2) CA-2026-RED-BLUE
D2 =SUBSTITUTE(A2,"RED","BLUE",1) CA-2026-BLUE-RED
B3 =SUBSTITUTE(A3,"RED","BLU") EU-009-BLU
B4 =SUBSTITUTE(A4,"RED","BLUE") CA-2026-red (unchanged)
B5 =SUBSTITUTE(A5,"--","-") CA-2026-RED

How to check: In B2, the literal RED appears twice; neither instance is skipped because n is absent. C2 affects only the second uppercase occurrence. B4 stays unchanged because red and RED are not the same case. These are independently calculated expected text outputs, not screenshots or native PlanMaker test results. The first three behaviors are explicitly supported by the official examples.

Advanced use: one specific occurrence and nested cleanup

To change only the second duplicate color token:

=SUBSTITUTE("RED/RED/RED","RED","BLUE",2)

Expected: RED/BLUE/RED. When n is present, count occurrences of the exact OldText sequence from left to right, not rows or characters. The behavior is consistent with SoftMaker’s fourth-argument description.

To standardize an ID exported with inconsistent punctuation:

=SUBSTITUTE(SUBSTITUTE("CA--2026--RED","--","-"),"-","/")

Expected: CA/2026/RED. The inner function collapses double hyphens, then the outer replaces remaining single hyphens. Caution: Repeatedly using SUBSTITUTE for text cleanup changes only the literal patterns you name; it does not validate an ID’s structure. A dedicated parser may be better when delimiters vary unpredictably.

To remove a known prefix token while keeping the rest (A2 is still CA-2026-RED-RED):

=SUBSTITUTE(A2,"CA-","")

Expected: 2026-RED-RED. This illustrates how changing the replacement length can change the output length; unlike a character-position replacement, SUBSTITUTE searches for a complete specified sequence.

Troubleshooting: why did the formula not change my text?

Symptom Likely explanation What to check
Original text is returned OldText is absent or uses the wrong capitalization Compare upper/lower case exactly; inspect spaces and hyphens.
More changes than expected Optional n was left out Specify 1, 2, etc. to target one occurrence.
Wrong occurrence changed n describes matches in the text, not a cell index Recount occurrences from the start of the string.
A formula is displayed as text Entry not parsed as a formula Enter a leading = and confirm in a normal cell. SoftMaker guidance.
Formula shows an error Missing argument, misspelled function or invalid argument type Recheck syntax and the PlanMaker error glossary; exact edge-case outcomes require native checking.
Unwanted blank-looking results The replacement may be "" or the source may contain extra whitespace Inspect A2 and both quoted text arguments carefully.

Do not assume case-insensitive matching. If the original data mixes cases, consider first standardizing a separate copy for comparison and verify that the transformation won’t damage identifiers with meaningful capitalization.

SUBSTITUTE versus REPLACE, and Excel compatibility

SUBSTITUTE searches for a matching string; REPLACE uses starting position and character count. The PlanMaker 2021, 2024 and 2026 English references all document SUBSTITUTE(Text, OldText, NewText [, n]) and the same case-sensitive and optional-occurrence concepts. 2021 · 2024 · 2026. This shows documentation presence in those releases, not the first version that implemented the function.

Microsoft documents analogous semantics for Excel’s SUBSTITUTE function, including optional instance_num; Excel’s parameter label differs from PlanMaker’s n. Microsoft reference. SoftMaker documents opening and saving .xlsx and .xls, but that alone is not a tested round-trip guarantee for every formula, locale, or edition. File-format documentation. No PlanMaker execution, cross-product workbook import/export test, FreeOffice feature test, macOS/Linux/Windows edition comparison, or mobile test has been performed.

Official reference and verification

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

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