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

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Hey, wait!Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.