Count number of data between two columns

[bhenlee]

Hi Experts,

Need help on counting the data between two values on different cells.

So what i would like the formula to do is find the first occurrence of "B" and "L" in a row then count the number of columns between the two.

So if the first "B" is in A1 and the first "L" is in F1 then there are 4 columns/characters between them.

Hope I make sense. Thank you.

[AlKey]

Try this

Please Login or Register  to view this content.

[TudyBTH]

Hi,
something like this?
Capture.JPG

see attached

[bhenlee]

Thanks Alkey what if i would like to use just one formula to count the columns between the first occurrence of "B" and "L" (there could be different occurrence of both letters in the row)


Thank you

[Tony Valko]

what if i would like to use just one formula to count the columns between the first occurrence of "B" and "L" (there could be different occurrence of both letters in the row)

Not sure what you're asking.

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

[AlKey]

If you need to establish position of two letters then you need two formulas

[bhenlee]

Hi Tony well say in row 1

AAABAAAAAAAALLLLAAAAAB -- i would like to count the number of A's between the first occurrence of "B" and "L".. then as too what Alkey stated I would like to count the number of A's between the last occurrence of "L" and 2nd occurrence of B.

Yes Alkey i'd probably need two formulas

Thank you experts

[AlKey]

Try this
Formula in A3

Please Login or Register  to view this content.

v	A	B	C	D	E	F	G	H	I	J	K
1	A	A	B	A	A	A	A	A	L	L	L
2
3	5
4

[bhenlee]

Thank you Alkey!!! Works perfectly