Can I Spreadsheet?
← All how-to recipes

How to find the Nth occurrence of a character

✓ Verified in LibreOffice 25.8.7.3

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

AppFormulaNotes
Excel=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(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),3))Identical. Regex fans can use =REGEXEXTRACT-style position logic, but this is the portable way.
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.

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. Every formula here is confirmed by actually executing it.

Functions used

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

Related recipes

Related comparisons