Find the Nth Occurrence of a Character

All versionsFINDSUBSTITUTE

FIND locates the first match of a character. To find the 2nd, 3rd, or nth — the second slash in a path, the third comma in a record — mark that occurrence first with SUBSTITUTE, then FIND your marker.

Quick formula: to find the position of the Nth dash in A2:

=FIND("~", SUBSTITUTE(A2, "-", "~", N))

SUBSTITUTE replaces only the Nth dash with a rare marker (~); FIND then returns that marker’s position.

Functions used (tap for the full reference guide):

FIND SUBSTITUTE

The example

Finding the position of the 2nd dash.

	A	B
1	Text	Pos of 2nd '-'
2	A-100-X-9	6
3	one-two-three	8

The formula

The position of the 2nd dash:

=FIND("~", SUBSTITUTE(A2, "-", "~", 2)) // "A-100-X-9" → 6

How it works

Mark the target, then find the mark:

SUBSTITUTE(A2, "-", "~", 2) swaps only the 2nd dash for a character that won’t appear in the text (~). The other dashes stay.
FIND("~", …) returns the position of that unique marker — which is the position of the original 2nd dash.
Change the 2 to find any occurrence.
Combine with MID/LEFT to extract the chunk between, say, the 2nd and 3rd delimiter.

Pick a marker that can’t collide. Use a character you’re sure isn’t in the data — ~, |, or CHAR(1). If it could appear, SUBSTITUTE might find the wrong spot.

Try it: interactive demo

Live demo

Type text, a character, and which occurrence.

Text (A2)

Char

Position:

Variations

Extract between the Nth and (N+1)th delimiter

Combine the positions with MID to slice a field.

Count occurrences first

How many dashes are there?

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

Case-insensitive search

Use SEARCH instead of FIND for the marker hunt if letters vary in case.

Pitfalls & errors

#VALUE! if the Nth occurrence doesn’t exist. Asking for the 4th dash when there are 3 errors. Guard with IFERROR or check the count first.

Marker collision. If your chosen marker already appears in the text, FIND may return the wrong position. Pick a character that can’t occur.

FIND is case-sensitive. For letters, decide between FIND (exact case) and SEARCH (any case).

Practice workbook

📊

Download the free Find the Nth Occurrence of a Character practice workbook

Coded strings with the live FIND+SUBSTITUTE nth-occurrence formula, the count and extract-between variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find the nth occurrence of a character in Excel?

Mark it then find it: =FIND("~", SUBSTITUTE(A2, "-", "~", N)). SUBSTITUTE replaces only the Nth dash with a unique marker, and FIND returns its position. Works in every version.

How do I count how many times a character appears?

Use =LEN(A2)-LEN(SUBSTITUTE(A2,"-","")), the length difference after removing the character.

What if the nth occurrence doesn't exist?

FIND returns #VALUE!. Wrap the formula in IFERROR, or check the occurrence count with the LEN/SUBSTITUTE method before extracting.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Function references: FIND · SUBSTITUTE