Partial text frequency counting in reports

Hi there,

I am trying to extract thefrequency of a certain abnormality (adenoma) out of multiple reports. The problem is that due to the pharsing in the reports ( ex. no adenoma found) I get false hits if I just look for the word adenoma in the cell. Is there a way to efficiently discern between these two? The database consist of 10k+ reports so I am unable to check them all manually.

I tried to use this formula someone on this forum was kind enough to send me, to find the frequency of mentions of no adenoma for example: =SUMPRODUCT(ISNUMBER(SEARCH("no* adenoma";TRIM(MID(SUBSTITUTE($B4;":";REPT(" ";LEN($B4)));(ROW($B$1:$B$10)-1)*LEN($B4);LEN($B4)))))+0)
This formula seems to work for simple one line answers in the sample worksheet at the time, but once I checked a sample of the complex reports it doesn't work anymore.

In the worksheet you will find fictive examples of reports in terms of pharsing.

Thanks for the help!

First, the references in your columnd D are off/wrong.

Also, substitute is case sensitive, isn't it? Maybe that's why it's not working anymore?

Lazy man's solution attached. Does that work?

I guess I won't sleep well tonight after reading so much about polyps and biopsy from anus

Attached slightly improved.

Originally Posted by JohnTopley

It is not surprising the original formula does not work as the "new" data is in a completely different format (free format vs structured) to that in your original post.


See attached where the original formula works (Yellow highlight) as the colon ":") is used to delimit (divide) the text into discrete "sets" of data.

In the non-highlighted rows the delimiter is a full stop and this gives the correct results.

NOTE: as per the original, the calculation of "adenomas" is a subtraction of column D from column C.

VBA solution might be a (better) option.

A I see I'm still pretty new to excel and didnt catch on to that thanks for the tip!

Originally Posted by bsalv

a VBA-solution

Thank you this seems to work perfectly! I've never heard of VBA before, but if I learn a few things I think I could use this to attain my goal in the actual database as well as some others!

Originally Posted by RaulSerg

Lazy man's solution attached. Does that work?

I guess I won't sleep well tonight after reading so much about polyps and biopsy from anus

I guess I forgot to save because I did fix that before hand whoops. Thanks for your effort, I hope you can still sleep soundly tonight :P

I think I can achieve what I want to achieve if I learn some VBA skills and build further upon bsalv's work, so solved for now

//edit//..