How to find SECOND occurrence in a string?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
 
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 /
 
Wow, beautiful solution, and so simple. I am often surprised at how some of
the best Excel solutions are the shortest. Thanks!
 
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!
Click to expand...
 
Back
Top