Find 2nd (or nth) Occurrence of Character In Excel & Google Sheets

This tutorial will demonstrate how to find the second or nth occurrence of a character in Excel & Google Sheets.

Find nth Occurrence of Character in Text

To find the nth occurrence of a character (or string of characters), we will use the FIND and SUBSTITUTE functions. For example, to get the position of the 2^nd occurrence of the “d” character, we put the following formula in cell C3:

=FIND(CHAR(140),SUBSTITUTE(B3,"d",CHAR(140),2))

We can generalize the formula more to find a list of characters and occurrences:

=FIND("~",SUBSTITUTE(B4,C4,"~",D4))

The formula has two-parts, which are separately explained below:

SUBSTITUTE Function Part

We used the SUBSTITUTE function to replace the occurrence of a specified character (“c”), in the text string, with the unique character (“~”).

=SUBSTITUTE(B3,C3,"~",D3)

Note: Just make sure that the unique character, you are using in the formula is not already in the text string.

FIND Function Part

Now, we put the FIND function before the SUBSTITUTE function to get the desired result.

By replacing the specific instance of the character (here let’s say its “c”) with a unique character (“~”), we can now simply use the FIND function to look for the unique character (“~”). The FIND function will return the position of that unique character (“~”) in the text string.

=FIND("~", E3)

Of course, this returns the 2^nd occurrence of “c”, which is 27. And the formula can generally be used to return the position of the nth occurrence of a character in Excel.

Find nth Occurrence of Character In Google Sheets

The formula to find the nth occurrence of character from a text string works exactly the same in Google Sheets as in Excel: