Find 2nd (or nth) Occurrence of Character In Excel & Google Sheets
Written by
Reviewed by
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 2nd 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 2nd 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:
