Find cells that contain alliteration

[ollyhughes1982]

Hi,

Re the below screenshot, would anybody please be able to advise how to find and count cells in a particular column that contain alliteration. In this example, manually counting shows that the result should be 6. Cardiff parkrun, Newport parkrun and Sharpham Fields Road parkrun do not contain alliteration, the other 6 do.

I've tried a few things, but nothing is working.

Thanks in advance.

Olly.

 

[ollyhughes1982]

OK, thanks

 

[lrobbo314]

How about this?

New__Document (11).xlsx
	A	B	C	D	E
1	Name	Helper Column		Alliteration Count:	6
2	Bannockburn Bush parkrun	1
3	Batemans Bay parkrun	1
4	Benalla Botanical Gardens parkrun	1
5	Bendigo Botanic Gardens parkrun	1
6	Cardiff parkrun	0
7	Newport parkrun	0
8	Pakapakanthi parkrun	1
9	Parkville parkrun	1
10	Sharpham Fiels Road parkrun	0
Sheet3

Cell Formulas
Range	Formula
E1	E1	=SUM(B2:B10)
B2:B10	B2	=LET(s,UPPER(LEFT(FILTERXML("<i><I>" & SUBSTITUTE(A2," ","</I><I>") & "</I></i>","//I"),1)),o,INDEX(s,SEQUENCE(COUNTA(s),,2)),SUM(IFERROR((s=o)+0,0)))

 

[Fluff]

Filterxml is not available on a Mac, it's PC only.

 

[ollyhughes1982]

Filterxml is not available on a Mac, it's PC only.

Frustrating! Thanks both for trying.

 

[Fluff]

However borrowing your formula, this seems to work

=LET(Txt," "&A2&" ",Qty,SEQUENCE((LEN(Txt)-LEN(SUBSTITUTE(Txt," ","")))-1),l,LEFT(REPLACE(LEFT(Txt,FIND("~",SUBSTITUTE(Txt," ","~",Qty+1))-1),1,FIND("~",SUBSTITUTE(Txt," ","~",Qty)),""),1),o,INDEX(l,SEQUENCE(COUNTA(l),,2)),SUM(IFERROR((l=o)+0,0)))

 

[lrobbo314]

Very cool @Fluff. Teamwork!

 

[ollyhughes1982]

Brilliant. Two heads are better than one! Thanks so much, both. If I hit any trouble, I'll get back in touch.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[ollyhughes1982]

I get '1' as the result on mine. Screenshots below. Have I done something wrong? I've put the file in a link if you want to have a look. It's likely I've done something wrong, rather than you guys.

Link: New Australian Challenges - List & Descriptions etc.xlsx

Thanks again.

 

[Fluff]

That needs to go in a helper column copied down & then sum the helper column.