How to find the nth occurrence of a character in Excel

Position of the 2nd hyphen with FIND and SUBSTITUTE

Column A holds product SKUs built from parts: style, model, color, size. FIND("-",A2) would give 3, the first hyphen. To reach the second, SUBSTITUTE(A2,"-","#",2) turns only the second hyphen into a #, so TX-204-BLK-L becomes TX-204#BLK-L. FIND then looks for the #.

Put the instance number in a cell (D2 here) instead of typing 2, and you can change which hyphen you want without editing formulas. This works in every version of Excel.

Text before and between occurrences

Once you have a position, LEFT and MID do the cutting. Everything before the second hyphen is LEFT(A2, position - 1), which gives TX-204, the style and model.

Text between the 2nd and 3rd hyphens needs both positions: start one character after the 2nd and take (3rd - 2nd - 1) characters. It is long, but each piece is the same pattern.

Excel 365: TEXTAFTER and TEXTBEFORE with an instance number

In Excel for Microsoft 365 and Excel 2024, TEXTAFTER(text, delimiter, instance_num) and TEXTBEFORE do all of this without positions. =TEXTBEFORE(A2,"-",2) returns TX-204. Nest them to get the middle piece: =TEXTBEFORE(TEXTAFTER(A2,"-",2),"-") returns BLK.

If the instance does not exist, both return #N/A. Their optional sixth argument, if_not_found, lets you return something else instead, or wrap them in IFERROR. If you have them, prefer these: they are far easier to read and to fix later.

Text after the last slash (file name from a path)

For the last occurrence, count how many there are and use that count as the instance number. LEN(B2)-LEN(SUBSTITUTE(B2,"/","")) counts the slashes: the length drops by one for each slash removed. Here B2 has 4, so the 4th slash is the last.

In Excel 365 a negative instance number counts from the end, so =TEXTAFTER(B2,"/",-1) returns invoice-0912.pdf, and =TEXTBEFORE(B2,"/",-1) returns the folder, Clients/Halden/2026/Q3. That is the method to use when paths have a different number of folders on each row, like B3 and B5.

Pick a marker that cannot appear in the text

The marker must never already be in the cell, or FIND stops at the wrong one. B4 is Jobs/Job #118 Pell St/photos/site-01.jpg. Swap the last slash for # and FIND finds the # in "Job #118" first, so the formula returns 118 Pell St/photos/site-01.jpg.

Use a character that your data never contains. "|" is used above; CHAR(1), a non-printing character, is the safest choice in long-lived workbooks.

When there are fewer occurrences than you asked for

CAP-12-RED has only two hyphens and BAG-3 only one. If SUBSTITUTE finds no nth instance it changes nothing, FIND finds no marker, and the result is #VALUE!. Decide what should happen: a blank, the whole text, or a message. IFERROR handles it, and the counting trick tells the reader why.