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

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 7, 2023

Download Example Workbook

Download the example workbook

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))

Find 2nd Character Position

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

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

Find Nth Occurrence of a character in Excel

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)

Find Nth Occurrence of a character SUBSTITUTE Function

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)

Find Nth Occurrence of a character FIND function Part

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.