Avatar of jana
jana
Flag for United States of America

asked on 

Find X occurrence of a character withing a text cell in Excel

Please advice correct formula to know the position or the occurrence of a specific character within an excel cell.

For example in the cell contents "Oh why? Is there any hope? Please, oh Please, Thanx!" (see pix below), we want to find the position of the 4th character "e".  It should be in the 41th position.

excel screen of contenst to search in with columns
The formulas working with are:
=FIND(C5,B3,FIND(C5,B3)+D5)
=SEARCHB(C5,B3,(SEARCHB(C5,B3,(SEARCHB(C5,B3,1)+1))+1))
=FIND(CHAR(1),SUBSTITUTE(B3,C5,CHAR(1),3))
=FIND(C5,B3, FIND(C5,B3)+3)

Open in new window


The first works if we want to find the 2nd or 3rd  occurrence of "e", but the 4th, 5th, etc., doesn't work.
(also if we want to find "?" or "," or ".", or "!" also doesn't return the correct position)

Please advice on the correct formula to find within a text cell any position of any character.

Thank you in advance.
Microsoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
jana

8/22/2022 - Mon