Spreadsheet Practice

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

Formula (Excel and Google Sheets)

=FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))

Count the characters, then mark the last one: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))).

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

LEN(A2)-LEN(SUBSTITUTE(A2,"/","")) counts the slashes: the length drops by one for each slash removed. That count is the instance number of the last slash, so SUBSTITUTE replaces just that one with CHAR(1), and FIND returns where it is.

=FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))

Result38

  1. CHAR(1)""

    CHAR: Character for a code number.

    • number1
  2. A2"https://northwind.example/shop/chairs/oak-4471"

    The value in A2.

  3. CHAR(1)""

    CHAR: Character for a code number.

    • number1
  4. LEN(A2)46

    LEN: Number of characters.

    • textA2"https://northwind.example/shop/chairs/oak-4471"
  5. SUBSTITUTE(A2,"/","")"https:northwind.exampleshopchairsoak-4471"

    SUBSTITUTE: Replaces matching text.

    • textA2"https://northwind.example/shop/chairs/oak-4471"
    • old_text"/"
    • new_text""
  6. LEN(SUBSTITUTE(A2,"/",""))41

    LEN: Number of characters.

    • textSUBSTITUTE(A2,"/","")"https:northwind.exampleshopchairsoak-4471"
  7. LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))5

    The - operator subtracts.

  8. SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))"https://northwind.example/shop/chairsoak-4471"

    SUBSTITUTE: Replaces matching text.

    • textA2"https://northwind.example/shop/chairs/oak-4471"
    • old_text"/"
    • new_textCHAR(1)""
    • instance_numLEN(A2)-LEN(SUBSTITUTE(A2,"/",""))5
  9. FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/",""))))38

    FIND: Position of text, case-sensitive.

    • find_textCHAR(1)""
    • within_textSUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))"https://northwind.example/shop/chairsoak-4471"

In Google Sheets

The same formula works in Google Sheets: =FIND(CHAR(1),SUBSTITUTE(A2,"/",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2,"/","")))). 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,"/",-1))+138, 34, 25…Excel 365: instance -1 is the last slash, so the length of the text before it, plus one, is its position.

Common mistakes

  • Text with no slash gives an instance of 0, and SUBSTITUTE returns #VALUE!. Wrap the formula in IFERROR(…,0).
  • To search for a word rather than one character, divide the length difference by the word's length before using it as the instance.

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 last occurrence of a character

In B2, return the position of the last slash in the URL and fill down to B6.

Questions

How do I get the text after the last slash?

Use the position with MID, =MID(A2,B2+1,LEN(A2)), or skip it: =TEXTAFTER(A2,"/",-1).

How do I find the last space in a name?

Swap the character: =FIND(CHAR(1),SUBSTITUTE(A2," ",CHAR(1),LEN(A2)-LEN(SUBSTITUTE(A2," ","")))).

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.