Discerning between partial text and frequency counting

[losc]

Hi there! I am trying to collect the frequency of diagnosis from a datbase, the problem is these are al written reports. I want to count the frequency of No adenoma found, however I also need the frequency of adenomas found.

I used:
=(LEN(A2)-LEN(SUBSTITUTE(A2;"adenoma";"")))/LEN("adenoma") to find the frequency of the word adenoma. However in the same text there is also a mention of No polyps or adenoma, this results in a false addition of one adenoma. I tried getting the amount of no adenomas by replacing "adenoma" with "No* adenoma" and subtracting it from the adenoma frequency column. But this adjusted formula would not count the phrase: No polyps or adenoma.

Any tips on how to solve this?

Thanks!

[AliGW]

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

[losc]

Thanks! I made a simple small sample document due to the database being confidential

[samba_ravi]

b2=SUMPRODUCT(ISNUMBER(SEARCH("adenoma",TRIM(MID(SUBSTITUTE($A2,":",REPT(" ",LEN($A2))),(ROW($A$1:$A$10)-1)*LEN($A2),LEN($A2)))))+0)
c2=SUMPRODUCT(ISNUMBER(SEARCH("no*adenoma",TRIM(MID(SUBSTITUTE($A2,":",REPT(" ",LEN($A2))),(ROW($A$1:$A$10)-1)*LEN($A2),LEN($A2)))))+0)
d2=b2-c2
try above formula

[losc]

b2=SUMPRODUCT(ISNUMBER(SEARCH("adenoma",TRIM(MID(SUBSTITUTE($A2,":",REPT(" ",LEN($A2))),(ROW($A$1:$A$10)-1)*LEN($A2),LEN($A2)))))+0)
c2=SUMPRODUCT(ISNUMBER(SEARCH("no*adenoma",TRIM(MID(SUBSTITUTE($A2,":",REPT(" ",LEN($A2))),(ROW($A$1:$A$10)-1)*LEN($A2),LEN($A2)))))+0)
d2=b2-c2
try above formula

Thank you it seems to work! I'll try it in the actual database to see if it works there