Multiple IF(OR(... , is there a simpler way?

[Nashpotatoes]

I am using excel to search multiple text reports (histology reports) that are normally 8-10 sentences long. I want to search for specific phrases such as "chronic scarring", or "calcification", include indeterminate findings as a separate categories, and exclude instances these phrases are preceded by things like "there is no",

So far, what I have is summarised below, but I feel there could be an easier way to:
1) Simplify my formula searching for multiple variations on "there is no sign of", so that if I come across a new phrase that I can add it easily
2) Or simplify the process so that I do not need to extract sentences from the full report into a separate column

I would be very grateful for any help.

-	A	B	C	D	E	F
1	Full Report	Extracted sentence: Calcification	Extracted sentence: Chronic scarring	Calcification?	Chronic scarring?
2	There is calcification. There is no chronic scarring.	There is calcification	There is no chronic scarring	Yes	No
3	There is borderline chronic scaring. No evidence of calcification	No evidence of calcification	There is borderline chronic scaring	No	Borderline
4	Signs of possible calcification. Evidence of chronic scarring	Signs of possible calcification	Evidence of chronic scarring	Possibly	Yes

My current formulas looks like this:

In B, to extract sentences I have:

=IFERROR(TEXTJOIN(". ",TRUE,FILTERXML("<t><s>"&SUBSTITUTE($A2,".","</s><s>")&"</s></t>","//s[contains(translate(.,'C','c'), 'calcification')]")),"")

In D column I have:

=IF(ISBLANK(B2),"no",IF(OR(ISNUMBER(SEARCH("there is no",B2)),ISNUMBER(SEARCH("no evidence",B2)),ISNUMBER(SEARCH("no features of",B2)),ISNUMBER(SEARCH("no sign of",B2))),"no",(IF(OR(ISNUMBER(SEARCH("borderline",B2)),ISNUMBER(SEARCH("some evidence of",B2))),"Borderline",(IF(OR(ISNUMBER(SEARCH("possibly",B2)),ISNUMBER(SEARCH("may be",B2)),ISNUMBER(SEARCH("suspicious",B2)), ISNUMBER(SEARCH("cannot be exluded",B2)), ISNUMBER(SEARCH("although cannot exclude",B2))),"Possibly","yes")))))

Nb. Sometimes the reports no not mention the phrases "calcification" or "chronic scarring" at all

 

[DanteAmor]

1) Simplify my formula searching for multiple variations on "there is no sign of", so that if I come across a new phrase that I can add it easily
2) Or simplify the process so that I do not need to extract sentences from the full report into a separate column

To add a new phrase can be in a data range. You can create the phrases, for example in the cell range from E2 to E50 and in the next column put the status.
Check the following, only one formula is required:

Dante Amor
	A	B	C	D	E	F
1	Full Report	Calcification?	Chronic scarring?		Phrases	Status
2	There is calcification. There is no chronic scarring.	Yes	No		there is no	No
3	There is borderline chronic scarring. No evidence of calcification	No	Borderline		no evidence	No
4	Signs of possible calcification. Evidence of chronic scarring	Possibly	Yes		no features of	No
5	Is although cannot exclude calcification. Evidence of chronic scarring	Possibly	Yes		no sign of	No
6	May be chronic scarring. Is some evidence of calcification	Borderline	Possibly		Borderline	Borderline
7	Is borderline calcification. although cannot exclude of chronic scarring	Borderline	Possibly		some evidence of	Borderline
8					possible	Possibly
9					Possibly	Possibly
10					may be	Possibly
11					suspicious	Possibly
12					cannot be excluded	Possibly
13					although cannot exclude	Possibly
14
Hoja1

Cell Formulas
Range	Formula
B2:C7	B2	=IFERROR(INDEX($F$2:$F$50,MAX((ISNUMBER(SEARCH(IF($E$2:$E$50<>"",$E$2:$E$50),IF(ISNUMBER(SEARCH(B$1,LEFT($A2,FIND(".",$A2)))),LEFT($A2,FIND(".",$A2)),MID($A2,FIND(".",$A2),999)))))*(ROW($F$2:$F$50)))-ROW($F$2)+1),"Yes")
Press CTRL+SHIFT+ENTER to enter array formulas.

----
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Nashpotatoes]

To add a new phrase can be in a data range. You can create the phrases, for example in the cell range from E2 to E50 and in the next column put the status.
Check the following, only one formula is required:

Dante Amor
	A	B	C	D	E	F
1	Full Report	Calcification?	Chronic scarring?		Phrases	Status
2	There is calcification. There is no chronic scarring.	Yes	No		there is no	No
3	There is borderline chronic scarring. No evidence of calcification	No	Borderline		no evidence	No
4	Signs of possible calcification. Evidence of chronic scarring	Possibly	Yes		no features of	No
5	Is although cannot exclude calcification. Evidence of chronic scarring	Possibly	Yes		no sign of	No
6	May be chronic scarring. Is some evidence of calcification	Borderline	Possibly		Borderline	Borderline
7	Is borderline calcification. although cannot exclude of chronic scarring	Borderline	Possibly		some evidence of	Borderline
8					possible	Possibly
9					Possibly	Possibly
10					may be	Possibly
11					suspicious	Possibly
12					cannot be excluded	Possibly
13					although cannot exclude	Possibly
14
Hoja1

Cell Formulas
Range	Formula
B2:C7	B2	=IFERROR(INDEX($F$2:$F$50,MAX((ISNUMBER(SEARCH(IF($E$2:$E$50<>"",$E$2:$E$50),IF(ISNUMBER(SEARCH(B$1,LEFT($A2,FIND(".",$A2)))),LEFT($A2,FIND(".",$A2)),MID($A2,FIND(".",$A2),999)))))*(ROW($F$2:$F$50)))-ROW($F$2)+1),"Yes")
Press CTRL+SHIFT+ENTER to enter array formulas.

----
NOTE XL2BB:
For the future, it would help greatly if you could give us the sample data in a form that we can copy to test with.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you very much for this, and for the advice about XL2BB, which I have used on different posts now. What I ended up going for was similar to your formula and using the table of phrases, but used IF(OR(COUNT IF... but on a cell that had already trimmed the large text field to the sentences containing the specific word. This also helped to add to the list of phrases without updating the formula.

1st in H column, to copy sentences containing the word calcification:

=IFERROR(TEXTJOIN(". ",TRUE,FILTERXML("<t><s>"&SUBSTITUTE($G2,".","</s><s>")&"</s></t>","//s[contains(translate(.,'C','c'), 'calcification')]")),"")

And then using the below formula to help decide whether phrasing used was positive, negative, or indeterminate (i.e. possibly):

=IF(H2="","no",IF(OR(COUNTIF(H2,"*"&(OFFSET(Phrases!$A$1,COUNTA(Phrases!$A$2:$A$40),0,-COUNTA(Phrases!$A$2:$A$40),1))&"*")),"no",IF(OR(COUNTIF(H2,"*"&OFFSET(Phrases!$C$1,COUNTA(Phrases!$C$2:$C$40),0,-COUNTA(Phrases!$C$2:$C$40),1)&"*")),"borderline",IF(OR(COUNTIF(H2,"*"&OFFSET(Phrases!$B$1,COUNTA(Phrases!$B$2:$B$40),0,-COUNTA(Phrases!$B$2:$B$40),1)&"*")),"possibly","yes"))))

Thanks