In Microsoft Excel, what formula would you use to find the Nth occurrence of a character (or string) in a string? Before we look at the final formula, we need to look at the Excel FIND and SUBSTITUTE functions. The final formula is near the bottom of this post.

FIND() Function

Here is how the FIND function works. The function FIND(text_string_to_find, text_to_look_in, k) returns the location of text_string_to_find in text_to_look_in, starting at the position k in the text_to_look_in string. FIND is case-sensitive. Note that the the SEARCH function is not case sensitive.

Here is an example of the FIND function.

SUBSTITUTE() Function

The substitute function is used when you don’t know the position of the text. When you omit the last argument, every occurrence of the old text is replaced by the new text.

Find the Nth Occurrence

If we use a combination of FIND and SUBSTITUTE we can get the Nth occurrence of a character in a string. Here is the formula: =FIND(CHAR(135),SUBSTITUTE($A$1,B4,CHAR(135),C4)). Note that CHAR(135) is just a character that is unlikely to be in your string, which is the double dagger. If you might have that character in your string, choose a different char from the ANSI character chart that you can find at the HTML Windows-1252 (ANSI) Reference at w3schools.com. Below is a screenshot of an example.

You can have a look at the article over at ExcelJet for an article that discusses this very topic.

SQL Server

If you need to do a similar parsing operation in T-SQL in SQL Server, have a look at our post here called SQL Find Nth Occurrence of Char in a String.

Python

In Python, we can Find the nth Occurrence of a Char (or chars) in a String. There is a find method in Python that we use to accomplish this. In Python, I have written a user-defined function.