=SUBSTITUTE(source-string, existing-string, new-string, occurrence)

The final occurrence argument is one-based. This is not a character position: occurrence 2 means the second found instance of the existing-string, wherever it occurs. Apple’s documentation also supports REGEX patterns in the search expression; that is a separate advanced feature, and this tutorial uses literal substrings to avoid confusing text replacement with capture-group syntax. See Apple SUBSTITUTE.

The function’s current English documentation is in Apple Formulas and Functions Help 15.4, reviewed 2026-10-11. This is a documentation version, not proof that the formula first appeared in Numbers 15.4. Apple’s Functions Help welcome describes its use alongside iWork apps on Mac, iPhone, iPad and iCloud.com. The individual reference does not certify identical behavior on every older Numbers release or browser/device build. No Mac, iPhone, iPad, or iCloud Numbers session was executed for this guide. Where an older release matters, check its formula editor and retest the sample.

Examples below use references inside one Numbers table. Apple’s cell reference guide documents A2 and A2:A6 for the current table, and qualified forms such as Catalog::A2 or Sheet 2::Catalog::A2 for cells in other tables or sheets. Select the cell in the editor when unsure about the qualifier. Numbers is table-centric; don’t replace :: with an Excel worksheet !.

Apple’s syntax guide shows commas between arguments for locales that do not use a decimal comma. When the decimal separator is a comma, use semicolons between function arguments you type manually. This changes the delimiter, not the order or meaning of arguments. Formula names are case-insensitive when typed, although Apple shows them in uppercase.

Create a table named Catalog with one header row and these five text values. Enter the codes as text, not numeric cells, so the leading zero in row 4 survives import.

Put the formula into a free cell in column B of the same table and fill/copy down as indicated. The codes follow the documented example pattern two-letter region, hyphen, four-character identifier, hyphen, two-character revision. This is a teaching assumption, not a promise that real imported SKUs always follow that structure.

In B2 enter:

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

Expected output in B2: CA2048R1. Copy the formula down the table:

The "" argument represents a zero-character replacement string; do not confuse it with LEFT/RIGHT’s string-length argument, where Apple documents a minimum of 1. Preserve normalized codes as text, since leading zeros in identifiers are meaningful.

To remove only the separator immediately before the revision label in row 2, supply the optional occurrence argument:

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

Expected output: CA-2048R1. The first hyphen remains; only the second is replaced. Compare:

=SUBSTITUTE("red-red-red","red","blue",2)

The expected result is red-blue-red: 2 identifies the second match, not the second letter or the number of replacements. For a code with only one hyphen, SUBSTITUTE(A2,"-","",2) would leave that code unchanged by Apple’s documented excessive-occurrence rule. To change the revision tag instead, =SUBSTITUTE(A2,"R1","R4") yields expected CA-2048-R4, but other rows only change when they contain that exact target substring.

• No change: The search text may not occur, may be typed differently, or the specified occurrence may exceed the number of matches. Inspect the source directly.
• More replacements than intended: Omit occurrence only when replacing every match is desirable; otherwise specify the occurrence index.
• Wrong replacement: Make sure the existing-string and new-string arguments are not swapped; changing "-" to "" removes separators, while the reverse does not mean the same thing.
• Invalid occurrence: Apple’s current reference requires an occurrence number ≥ 1 when supplied. Do not teach zero or negative occurrence numbers as valid.
• Text identifiers: A result that looks numeric is still not automatically a safe number to store. Leave order/part IDs as text to preserve zero padding.
• Unexpected spaces: SUBSTITUTE looks for the specified substring; it is not a generic whitespace cleanup. If source data contains varied spacing, inspect the input and consider a separate TRIM workflow when appropriate.
• REGEX patterns: Apple’s reference permits REGEX as existing-string, including capture groups. Such use requires its own correct pattern/replacement rules and validation; don’t assume arbitrary pattern strings are interpreted as regular expressions.

In Apple Numbers, SUBSTITUTE replaces matching content. Apple REPLACE operates on characters at a specified position; it is not simply another name for SUBSTITUTE. Excel and Google Sheets also provide SUBSTITUTE with an optional occurrence argument in common versions, but imported formula delimiters, table references, and any REGEX-related extensions should be checked separately. A Numbers formula using the REGEX function inside SUBSTITUTE is not necessarily portable to the same-looking function in Excel or Sheets.

• TEXTAFTER in Apple Numbers
• Apple REPLACE official reference — change by position instead of matching content.
• Apple REGEX.EXTRACT reference — distinct extraction function; an existing site guide uses the preserved regex-extract slug.

• Apple Numbers — function documentation
• Functions Help welcome
• cell reference guide
• syntax guide
• Apple REPLACE official reference
• Apple REGEX.EXTRACT reference

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

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