How to find the nth occurrence of a character in a string in Excel ?

Assume that you have a text in cell A1 and you wish you to find the 2nd occurrence of a specified character and display the position of it.

How to find the nth occurrence of a character in a string in Excel ?

In this example , lets have the cell A1 containing the following string

This is a Developer Publish Blog.

You have been provided with the task to display the 2nd occurrence of the character e and display its position.

You can use the FIND formula to achieve it.

In the cell B1 , enter the formula

=FIND(“e”,A1,FIND(“e”,A1)+1)

Press enter key. This should display the position of the 2nd occurrence of the character e.

image

FIND(“e”,A1)+1 indicates that it is the 2nd occurrence that we are interested in. If you want to get the 3rd occurrence , you can increase the value from 1 to 2 (FIND(“e”,A1)+2).

Leave A Reply

Your email address will not be published. Required fields are marked *

You May Also Like

In this article, you will learn about the SKEW.P function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the SKEW function, the formula syntax and usage of the function in Microsoft...
In this article, you will learn about the RANK.EQ function, the formula syntax and usage of the function in Microsoft...
  • Excel
  • November 23, 2021