How to find SECOND occurrence in a string?

[Guest]

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. 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

 

[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 /

 

[Guest]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!

Biff

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