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