How to Find 2nd, 3rd or Nth Occurrence of a Character in a String

To find the first occurrence of a character in Excel we use the FIND of SEARCH function in Excel. But if we want to find the second occurrence of a character in excel, both functions fail to do so. They need help to find the second, third or Nth occurrence of a character in Excel.

In this article, we will learn how to find the position of a Nth occurrence of a character in a string in Excel.

Generic Formula for finding the position of the Nth occurrence of a character

=SEARCH("~", SUBSTITUTE(string,character,"~",occurrence))

"~": This is a special character that will not appear in the string. You can use any character or symbol that you are sure will not appear in the string you are going to search.

String: This is the string in which you want to search for the occurrence of character.

Character: It is the character whose occurrence you want to search in the string.

Occurrence: It is the number of the occurrence of the character in the string.

Now that we are familiar with the formula and its argument let's use it in an example to understand how it works.

Example: Find the Second, Third and Nth Occurrence of Given Characters in the Strings.

Here we have some strings in range A2:A4. In cell C2, C3, and C4 we have mentioned the characters that we want to search in the strings. In D2, D3, and D4 we have mentioned the occurrence of the character. In the adjacent cell I want to get the position of these occurrences of the characters.

Write this formula in cell E2 and drag it down.

=SEARCH("~", SUBSTITUTE(A2,C2,"~",D2))

This returns the exact positions (19) of the mentioned occurrence (4) of the space character in the string.

How does it work?

The technique is quite simple. As we know the SUBSTITUTE function of Excel replaces the given occurrence of a text in string with the given text. We use this property.

So the formula works from the inside.

SUBSTITUTE(A2,C2,"~",D2): This part resolves to SUBSTITUTE("My name is anthony gonsalvis."
," ","~",4). Which ultimately gives us the string "My name is anthony~gonsalvis."

Note that the fourth occurrence of space is replaced with "~". I replaced space with "~" because I am sure that this character will not appear in the string by default. You can use any character that you are sure will not appear in the string. You can use the CHAR function to insert symbols.

Now the SEARCH function has SEARCH("~","My name is anthony~gonsalvis."). Now that the character "~" appears only once at the position 19, we get our answer as 19.

Similarly, all the formulas are solved.

If you want to use a case sensitive search then use the FIND function instead of SEARCH.

=FIND("~", SUBSTITUTE(A2,C2,"~",D2))

So yeah guys, this is how you can find the Nth occurrence of any given character in a string in Excel. I hope it was helpful. If you have any doubt regarding this topic or have any other Excel/VBA related query, please feel free to share it in the comments section below.

Related Articles:

How to Find Nth Occurrence in Excel : How do we LOOKUP a second match or 3rd or nth? In this article, we will learn how to get the Nth occurrence of a value in range. We don't use normal lookup techniques. To find Nth occurrence

How to do Running Count of Occurance in Excel : Count of running occurrence, what we mean by that. We will try to get occurrence of some text or value in a range by its position. For example, on first occurrence of “A”, we will tag it as 1, on second as 2, on third occurrence 3

How to Retrieve Every Nth Value in a Range in Excel : To retrieve every Nth value from a range in Excel, we use the INDEX and ROW function together. This formula gets all the occurrences of from the given range.

Find nth largest with criteria & nth smallest with criteria in excel : while working with numbers in data numbers, sometimes given a condition i.e. when we need to look up for the 2nd highest value given . You can perform the solution to this problem easily using the excel functions as explained below.