Find nth Occurrence of a character in a ROw

[bhenlee]

hi Experts,

How do i find the second/3rd/4th occurrence of the "B" in row 6?


Thank you

[Tony Valko]

Post some sample data and tell us what results you expect.

[FlameRetired]

With data in say C:N and the designated in column B array enter this in A1 and fill down.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

[bhenlee]

Thank you FlameRetired but how do i get the cell equivalent?

Tony say in row 1 (A:K) i have

B A A A L L L A A A B

what I'm trying to do is count the number of A's between the third occurrence of L and 2nd occurrence of B

So i figured how to find the 2nd occurrence of L but not the 2nd B.

[Tony Valko]

Try this array formula**:

=COUNTIF(INDEX(1:1,SMALL(IF(A1:K1="L",COLUMN(A1:K1)),3)):INDEX(1:1,SMALL(IF(A1:K1="B",COLUMN(A1:K1)),2)),"A")

This array formula** entered in

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Assumes there will always be a 3rd instance of L and a 2nd instance of B.

[bhenlee]

Thanks Biff.. that did the trick

[Tony Valko]

You're welcome. Thanks for the feedback!