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

"Bill_S" wrote in message
...
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?

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 /



On Mar 19, 10:08 am, Bill_S wrote:
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?

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


"T. Valko" wrote:

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

"Bill_S" wrote in message
...
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?

You're welcome. Thanks for the feedback!

Biff

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


"T. Valko" wrote:

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

"Bill_S" wrote in message
...
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?