Counting blocks rather than amounts

[JA-England]

I am trying to determine a formula that will help me count the number of spells in a given range. For example if I need to count the spells of the letter 'a' in the following sequence.

a a b c a b c c b a a

Although the number of a's is 5 it is actually in 3 blocks. I gather the same formula will account for number.

Cheers

 

[plettieri]

I see no answers to yr query yet, so i have a few questions (for my benefit anyway) maybe for other as well.

1. I trust all these characters you used in yr example are in one cell.

2. So you are looking to count the number of frequencies (blocks)that "a" or "aa" or "aaa", etc appear in given string. Is that correct?

pll

 

[IML]

Untested guess answer:

=ROUNDUP((LEN(A1)-LEN(SUBSTITUTE(A1,"a","")))/2,0)


assumes your list is in A1.

 

[JA-England]

Sorry, should have made it clear. They are all in separate cells.

Thanks for trying.

 

[plettieri]

I seem to get a different count when i change the input to something a little different

Book1
	A	B	C	D	E	F	G
6	a a b c a b c c b a a
7					3	ok
8
9
10	a a b c a b c c b a a a a a a b a a b
11					6
12
13				I think the answer should be 4 here
14
15
Sheet1

pll

 

[plettieri]

Is this what you need?

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N
17
18
19
20			a	a	b	a	a	a	a	b	c	a
21
22
23		so the answer you want is 3
24
25
Sheet1

pll

 

[JA-England]

Thats the one?

Exactly.

You guys are on it over there.

 

[JA-England]

Does it make any difference if I want it to work vertically, through a range A1:Z5?

This to loop down on a column basis, so if it starts at B5 and continues into C1 this counts as one spell.

 

[Mark W.]

Book3.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	a	a	b	c	a	b	c	c	b	a	a										3
2	a	a	b	c	a	b	c	c	b	a	a	a	a	a	a	b	a	a	b		4
3
4
Sheet1

 

[Mark W.]

This also works... and, is a bit simplier...

Book3.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y
1	a	a	b	c	a	b	c	c	b	a	a										3
2	a	a	b	c	a	b	c	c	b	a	a	a	a	a	a	b	a	a	b		4
3
4
Sheet1