How to find SECOND occurrence in a string?

B

Bill_S

I need a formula to find the position of the SECOND occurrence of a character
in a string. I can find the first occurrence using the FIND function no
problem but I have no idea how to find the position of the second occurrence
formulaically. Is there a formula to do that without running VBA?
 
T

T. Valko

Try this:

A1 = abcdabc

Find position of 2nd "a":

=FIND("a",A1,FIND("a",A1)+1)

Result = 5

Note that FIND is case sensitive. If you want to exclude case sensitivity
replace FIND with SEARCH.

Biff
 
T

Tyla

Say you have the word "supercalifragilistic" in Cell C6.
To find the second occurrence of the letter "a", you could use the
formula:
= FIND("a",C6,1) + FIND("a",MID(C6,FIND("a",C6,1)+1,LEN(C6)),1)
which will return the value of '12'. Others may have a simpler
formulas, however.

/ Tyla /
 
B

Bill_S

Wow, beautiful solution, and so simple. I am often surprised at how some of
the best Excel solutions are the shortest. Thanks!
 
T

T. Valko

You're welcome. Thanks for the feedback!

Biff

Bill_S said:
Wow, beautiful solution, and so simple. I am often surprised at how some
of
the best Excel solutions are the shortest. Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top