Text
Find the Nth occurrence position of a character (SUBSTITUTE + FIND)
Locate the exact 1-based character index of the 2nd, 3rd, or Nth delimiter ("-", "/", "_") in a string using SUBSTITUTE's instance_num argument.
Why SUBSTITUTE(..., instance_num) unlocks Nth-character positions
FIND("-", A2) only accepts a starting character offset—not an occurrence count! Meanwhile, SUBSTITUTE(text, old_text, new_text, instance_num) has a 4th argument that replaces ONLY the Nth occurrence of old_text.
Swapping ONLY the Nth "-" into a control marker CHAR(1) via SUBSTITUTE(A2, "-", CHAR(1), N) lets FIND(CHAR(1), ...) pinpoint the exact character index of that Nth hyphen in versions supporting these functions of Excel and Arte Sheets.
| Code (A) | Delimiter | N (B) | Nth Position | Prefix Before Nth |
|---|---|---|---|---|
| US-WEST-2026-0042 | "-" | 1 | 3 | US |
| US-WEST-2026-0042 | "-" | 2 | 8 | US-WEST |
| US-WEST-2026-0042 | "-" | 3 | 13 | US-WEST-2026 |
| US-WEST-2026-0042 | "-" | 4 | 0 (not found) | — |
Nth character position and TEXTBEFORE companion formulas
Wrap FIND directly inside IFERROR(..., 0) so strings with fewer than N delimiters return 0 cleanly instead of #VALUE!.
=IFERROR(FIND(CHAR(1), SUBSTITUTE(A2, "-", CHAR(1), B2)), 0)Extract text before or after the Nth delimiter directly
When you want the substring rather than the integer position, TEXTBEFORE(A2, "-", B2) does it in one call.
=IFERROR(TEXTBEFORE(A2, "-", B2), A2)Inputs and interpretation
The marker CHAR(1) must not occur in the original text. Use a positive integer occurrence count and a single-character delimiter for this example. A result of zero means not found or invalid input caught by IFERROR, not a valid position. TEXTBEFORE requires a version that supports it.
Try the example
Use the original values or change them to test your understanding.