partial text frequency counting

[losc]

Hey there! I am trying to count the frequency a certain diagnosis (adenoma) is found within a report. The problem is that sometimes the report contains no adenoma found of no polyp or adenoma found, this can be worded in various ways. The data base I am using is extremely large so I cannot check everything manually. I made a fictive worksheet to showcase the problem you can see below.
My current formula is;=SUMPRODUCT(ISNUMBER(SEARCH("no* adenoma";TRIM(MID(SUBSTITUTE($B3;":";REPT(" ";LEN($B3)));(ROW($B$1:$B$10)-1)*LEN($B3);LEN($B3)))))+0) however this does not seem to work consistently for either formula. The adenoma formula is pretty much the same just with different ranges and no* removed. I don't really understand why the wildcard does not work.

Any tips on how to solve this? Thanks in advance!

forum sample adenoma.xlsx
	A	B	C	D	E	F
1	number	Report	Amount of adenomas	Amount of no adenomas	manual count adenoma freq	manual count no adenoma freq
2	1	Colonoscopy: biopsy performed 50 cm from anus. No polyp or adenoma. Tubulovillieus adenoma in biopsy 2. Biopsy 3 hyperplastic polyp. No adenoma.	1	1	1	2
3	2	Fragemented hyperplastic polyp. No adenoma.	0	0	0	1
4	3	Villieus adenoma in 1. No polyp. Biopsy 2 no polyp or adenoma. Biopsy 3 no adenoma or polyp	0	0	1	2
5	4	I: Tubular adenoma low grade dysplasia II: biopsy 75 cm from anus: tubular adenoma low grade dysplasia III: Tubular adenoma low grade dysplasia IV: Tubular adenoma low grade dysplasia V: biopsy 60 cm from anus. No polyp or adenoma. VI: biopsy 50 cm from anus. hyperplastic polyp no adenoma.	6	2	4	2
6	5	sessile laesion. 50 cm from anus	0	0	0	0
7	6	Biopsy 40 cm from anus. Adenocarcinoma, no other polyps	0	0	0	0
8	7	I: Tubular adenoma low grade dysplasia II: no polyp or adenoma III: adenoma undefined IV: hyper plastic polyp, no adenoma	4	2	2	2
9	8	tubulovillieus adenoma in 1. Hyperplastic polyp in 2, no adenoma. No polyp or adenoma in 4.	0	0	1	2
10	9	Infectious activity in 1	0	0	0	0
11	10	I: Membrane biopsy coecum: tubulovillieus adenoma with low grade dysplasia. II: Polypectomy: tubular adenoma low grade dysplasia. Diamiter polyp 0,5 cm III: Polypectomy 80 cm from anus. Tubulovilieus adenoma low grade dysplasia; diameter polyp 1,5cm. IV: Biopsy membrane: No abnormalities (no polyp or adenoma)	4	1	3	1
Sheet1

Cell Formulas
Range	Formula
C2:C11	C2	=SUMPRODUCT(ISNUMBER(SEARCH("*adenoma",TRIM(MID(SUBSTITUTE($B2,":",REPT(" ",LEN($B2))),(ROW($B$1:$B$10)-1)*LEN($B2),LEN($B2)))))+0)
D2:D11	D2	=SUMPRODUCT(ISNUMBER(SEARCH("no* adenoma",TRIM(MID(SUBSTITUTE($B2,":",REPT(" ",LEN($B2))),(ROW($B$1:$B$10)-1)*LEN($B2),LEN($B2)))))+0)

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Partial text frequency counting in reports [SOLVED]
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.