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.
rayluvsAsked:
Who is Participating?
 
Rgonzo1971Commented:
then try
=FIND("|",SUBSTITUTE(B5,E2,"|",E3))

Open in new window

0
 
Rgonzo1971Commented:
Hi,

pls try
=FIND("|",SUBSTITUTE(B3,C5,"|",2))

Open in new window

Regards
0
 
rayluvsAuthor Commented:
attached is the excel with my formula and your ("EE").  Please see what am I doing wrong with your formula
CellPosition.xlsx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
rayluvsAuthor Commented:
Thank you very much!  Worked excellently!!!

Can you explain the formula a bit:

=FIND("|",SUBSTITUTE(B5,E2,"|",E3))
0
 
Rgonzo1971Commented:
substitute  replace then "e3"nth occurrence of E2 with "|" in the text in B5

find gives the position of the "|" in the substituted text

you can now replace "|" with Char(1) in both places if you think one of your texts could contain "|"
0
 
rayluvsAuthor Commented:
Thanx!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.