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

jana
jana used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
Hi,

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

Open in new window

Regards

Author

Commented:
attached is the excel with my formula and your ("EE").  Please see what am I doing wrong with your formula
CellPosition.xlsx
Top Expert 2016
Commented:
then try
=FIND("|",SUBSTITUTE(B5,E2,"|",E3))

Open in new window

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Can you explain the formula a bit:

=FIND("|",SUBSTITUTE(B5,E2,"|",E3))
Top Expert 2016

Commented:
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 "|"

Author

Commented:
Thanx!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial