troubleshooting Question

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

Avatar of jana
janaFlag for United States of America asked on
Microsoft ExcelMicrosoft Office
6 Comments1 Solution86 ViewsLast Modified:
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)

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.
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros