How to find the Nth occurrence of a character

✓ Verified in LibreOffice 25.8.7.3 Google Sheets returned something else (2026-08-30)

Locate the 2nd, 3rd, or Nth time a character appears — the position of the third slash in a path, the second hyphen in a code — so you can split or extract from there.

The formula

App	Formula	Notes
Excel (desktop)	`=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))`	SUBSTITUTE's 4th argument swaps only the Nth match for a marker (CHAR(1), which never appears in text); FIND then locates that marker. Change 3 to any N.
Google Sheets	`=FIND("§",SUBSTITUTE(A2,"-","§",3))`	Executed 2026-08-30: =FIND(CHAR(1),SUBSTITUTE("a-b-c-d","-",CHAR(1),3)) returned 1 in Google Sheets against 6 in LibreOffice. 1 is what FIND gives for an empty search string, so the CHAR(1) marker appears to reach FIND as nothing findable; Google’s CHAR page documents only “convert a number into a character according to the current Unicode table” and says nothing about control characters, so the divergence is undocumented. Use a marker your text lacks instead — § here, or CHAR(160). Documented Sheets syntax, carrying no executed result of its own.
LibreOffice Calc	`=FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))`	Identical.

How it works

Plain FIND and SEARCH only ever return the FIRST match, so the trick is to make the Nth match unique first. SUBSTITUTE(text, "-", CHAR(1), 3) replaces just the third hyphen with CHAR(1) — a control character that never occurs in normal text — leaving the rest untouched; FIND then reports where that marker sits. In "a-b-c-d" the hyphens are at positions 2, 4 and 6, so the third is 6. Swap the 3 for any occurrence number, and use CHAR(1) (or any character you're sure is absent) as the marker. This position is the launchpad for extraction: feed it into MID or LEFT to grab the text after the Nth delimiter, or combine with LEN to pull the final segment. If the character appears fewer than N times, FIND returns #VALUE! — wrap in IFERROR to handle short strings.

The Google Sheets alternative

Google Sheets needs different syntax for this task. The formula below is Sheets-specific — it was executed in Google Sheets (2026-08-30) against the same sample data as the worked example, and the value beside it is what Google returned. The LibreOffice column reads n/a because the formula is outside LibreOffice’s dialect, so there is nothing of ours to report there.

Formula	What it does	Returned by LibreOffice 25.8.7.3	Returned by Google Sheets (executed 2026-08-30)
`=FIND("§",SUBSTITUTE(A2,"-","§",3))`	A § marker the text cannot contain, instead of CHAR(1)	n/a (Sheets-only formula)	6 Google Sheets alternative (executed 2026-08-30)

Verified, not just documented

We ran =FIND(CHAR(1),SUBSTITUTE("a-b-c-d","-",CHAR(1),3)) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 6 — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. For the worked example Google Sheets returned 1, which is not what LibreOffice returned (6) — both values are shown as each engine produced them, and the disagreement itself is the finding. A further 1 row is a Google Sheets alternative: Sheets-specific syntax, executed in Google Sheets only, so the LibreOffice column reads n/a for it. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.

Functions used

FIND · CHAR · SUBSTITUTE — see full Excel, Google Sheets & LibreOffice compatibility for each.