Spreadsheet Practice

How to find the position of the nth occurrence of a character in Excel

Formula (Excel and Google Sheets)

=FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1))

Mark the nth occurrence with SUBSTITUTE and find the mark: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1)).

Result on the example
one result per row in B2, filled down to B6
Works in
Excel 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in B2, filled down to B6. Change any input and the result updates, or edit the formula to experiment.

How it works

FIND only returns the first match, so the trick is to make the nth slash unique. SUBSTITUTE's fourth argument replaces only that occurrence, here with CHAR(1), an invisible character that never appears in real text. FIND then returns its position, which is the position of the nth slash in the original.

=FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1))

Result12

  1. CHAR(1)""

    CHAR: Character for a code number.

    • number1
  2. A2"/sales/2026/q1/report.pdf"

    The value in A2.

  3. CHAR(1)""

    CHAR: Character for a code number.

    • number1
  4. $E$13

    The value in E1.

  5. SUBSTITUTE(A2,"/",CHAR(1),$E$1)"/sales/2026q1/report.pdf"

    SUBSTITUTE: Replaces matching text.

    • textA2"/sales/2026/q1/report.pdf"
    • old_text"/"
    • new_textCHAR(1)""
    • instance_num$E$13
  6. FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1))12

    FIND: Position of text, case-sensitive.

    • find_textCHAR(1)""
    • within_textSUBSTITUTE(A2,"/",CHAR(1),$E$1)"/sales/2026q1/report.pdf"

In Google Sheets

The same formula works in Google Sheets: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),$E$1)). We ran it in Google Sheets mode on the same data and it returns the same results.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=LEN(TEXTBEFORE(A2,"/",$E$1))+112, 13, 16…Excel 365: the length of everything before the nth slash, plus one.

Common mistakes

  • Replacing with a visible character like "#" breaks as soon as the text contains one. CHAR(1) avoids that.
  • If the text has fewer slashes than you ask for, SUBSTITUTE changes nothing and FIND returns #VALUE!. Wrap it in IFERROR(…,0).

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Find the position of the nth occurrence of a character

In B2, return the position of the slash whose number is in E1 (the 3rd slash), and fill down to B6.

Questions

How do I find the position of the last occurrence?

Count the slashes and use that as the instance: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))).

How do I return the text after that position?

Use MID: =MID(A2,B2+1,LEN(A2)), or TEXTAFTER with the instance number directly.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.