Find the Nth Occurrence of a Character in Excel

To find the nth occurrence of a character in Excel, replace just that occurrence with a marker and then FIND the marker: =FIND("#",SUBSTITUTE(A2,"-","#",2)) returns the position of the second dash. In Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER can skip straight to the text around the nth character. This guide is for anyone splitting codes, file paths or IDs that have the same separator more than once.

Why FIND alone only finds the first one#

FIND returns the position of the first match it sees. In the code NY-2024-INV-0087, =FIND("-",A2) returns 3, and there is no argument for "the second one". FIND does have an optional start position, so you can nest it:

=FIND("-",A2,FIND("-",A2)+1)

The inner FIND finds the first dash (3), and the outer FIND starts looking at character 4. The result is 8. This works for the second match, but it gets long fast: the third match needs three FINDs. The FIND function guide covers the start position, and the article on FIND vs SEARCH explains which one ignores capitals.

How to find the nth occurrence with FIND and SUBSTITUTE#

SUBSTITUTE has an optional fourth argument, instance_num, that replaces only one occurrence. That is the key to the whole trick:

=FIND("#",SUBSTITUTE(A2,"-","#",2))

Read it from the inside out:

1. SUBSTITUTE(A2,"-","#",2) changes only the second dash, so NY-2024-INV-0087 becomes NY-2024#INV-0087.
2. FIND("#", ...) returns the position of the only #, which is 8.

Change the 2 to any number to find any occurrence. Better still, point it at a cell so one formula handles every row:

In C2: =FIND("#",SUBSTITUTE(A2,"-","#",B2)), then fill down (Windows: Ctrl+D; Mac: ⌘D).

The marker must be a character that never appears in your text. If # might be in the data, use CHAR(1), an invisible control character: =FIND(CHAR(1),SUBSTITUTE(A2,"-",CHAR(1),B2)). The SUBSTITUTE function guide and the article on SUBSTITUTE vs REPLACE explain the other arguments.

How to get the text before or after the nth character#

A position on its own is rarely the goal. You usually want the text on one side of it. Wrap the position in LEFT or MID.

Text before the second dash (everything left of position 8):

=LEFT(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))-1)

Result: NY-2024. The -1 drops the dash itself.

Text after the second dash (start one character after it):

=MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,LEN(A2))

Result: INV-0087. Using LEN(A2) as the length simply means "take the rest"; MID stops at the end of the text.

Text between the second and third dash needs both positions: the start is after dash 2, and the length is the gap to dash 3.

=MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,FIND("#",SUBSTITUTE(A2,"-","#",3))-FIND("#",SUBSTITUTE(A2,"-","#",2))-1)

Result: INV. It works, but it is long. The guide to extracting text between two characters shows shorter ways.

The easier way: TEXTBEFORE and TEXTAFTER#

If you have Microsoft 365 or Excel 2024, TEXTBEFORE and TEXTAFTER do all of this in one step. Their third argument is the instance number:

A negative instance number counts from the end, so -1 means the last occurrence. That makes file paths easy: if A6 holds C:\Reports\2024\June\sales.xlsx, =TEXTAFTER(A6,"\",-1) returns sales.xlsx. The full guide to TEXTBEFORE and TEXTAFTER covers the other arguments, such as ignoring case.

Older Excel versions show #NAME? for these functions. If your file must open in Excel 2019 or earlier, stick with FIND and SUBSTITUTE.

How to find the last occurrence#

The last occurrence is just the nth one, where n is the number of times the character appears. Count it by comparing the length with and without that character:

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

For NY-2024-INV-0087 that is 16 − 13 = 3 dashes. Use the count as the instance number:

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

The result is 12, the position of the last dash. The same idea helps when you need the last word in a cell, where the separator is a space.

Example: get the folder from a file path#

File paths are a common real case, because the backslash appears many times. Say A6 holds C:\Reports\2024\June\sales.xlsx and you want everything up to the third backslash, the year folder:

=LEFT(A6,FIND("#",SUBSTITUTE(A6,"\","#",3))-1)

The third backslash is at position 16, so LEFT keeps 15 characters: C:\Reports\2024. In Microsoft 365 or Excel 2024, =TEXTBEFORE(A6,"\",3) gives the same result, and =TEXTBEFORE(A6,"\",-1) returns the whole folder path without the file name. The same pattern works for web addresses split by /, product codes split by ., and log lines split by |. Only the character inside the quotes changes.

Why the formula returns #VALUE! or the wrong position#

Most problems come from three causes:

• Fewer matches than n. If a code has only two dashes and you ask for the third, SUBSTITUTE changes nothing and FIND returns #VALUE!. Wrap it: =IFERROR(FIND("#",SUBSTITUTE(A2,"-","#",3)),"Not found"). TEXTAFTER returns #N/A instead, and its sixth argument sets a message, for example =TEXTAFTER(A2,"-",3,,,"none").
• Case. FIND and SUBSTITUTE are both case-sensitive. To find the second "x" in "Xbox max", wrap the text in LOWER: SUBSTITUTE(LOWER(A2),"x","#",2).
• Hidden spaces. A leading space shifts every position by one. Clean the column with TRIM first if the numbers look off by one.

Practise it#

The FIND and SUBSTITUTE pattern feels odd until you have typed it a few times. Practise FIND and SEARCH in a live spreadsheet: you'll locate characters and pull text out with LEFT and MID, and each answer is checked instantly. Then open the TEXTBEFORE and TEXTAFTER lesson to split text by the nth separator in one step. Start with the exercise on this page.