How to search for ‘positive’ text string

[Nashpotatoes]

I am using excel to search through a large database of typed reports and wish to extract or highlight when certain phrases are used, however when searching for text strings I keep retrieving instances where the phrases are used negatively. The reports are fairly consistent in some of the words they use in that they are only written by 2-3 people, however the order of the words used to convey things sometimes differ

For example, say I want to search my reports for ‘wear and tear’.
I would like the search to include instances where the reports say:
“There is evidence of wear and tear”
“There is wear and tear”
“Borderline wear and tear”
“Extensive wear and tear”

But not to count:
“There is no evidence of wear and tear”
“No sign of wear and tear”

Ideally I would like to be able to search for a phrase such as “wear and tear” in a passage of text and be able to highlight when it is preceded by either negative, positive, or ambiguous wording. And have a column of “Wear and tear?” Yes/no/maybe

Any help would be very much appreciated, thank you

 

[Dan_W]

As it so happens, Mr Excel himself made a video about sentiment analysis in Excel (see below). He references a free addin for Excel, and is available for users of Excel 2013+ (which according to your version information should be fine):

Hope that helps.

 

[bebo021999]

Column A from A3: text string
C1: text criteria
Column B from B3: positive text string
Column C from C3: negative text string

assum negative words are identified by: (Add more if needed)
neg = Array(" not ", " no ", "n't ", " none ")

Below code with separate into 2 columns, and highlight negative text

Option Explicit
Sub test()
Dim lr&, sWord As String, cell As Range, i&, neg
lr = Cells(Rows.Count, "A").End(xlUp).Row
sWord = " " & Range("C1").Value & " "
neg = Array(" not ", " no ", "n't ", " none ") ' add more negative words case
Range("B3:C100").ClearContents
For Each cell In Range("A3:A" & lr)
    If InStr(1, " " & LCase(cell) & " ", sWord) Then
        For i = 0 To UBound(neg)
            If InStr(1, " " & LCase(cell) & " ", neg(i)) Then
                cell.Offset(, 2).Value = cell.Value
                cell.Interior.ColorIndex = 7
                GoTo z
            End If
        Next
        cell.Offset(, 1).Value = cell.Value
    End If
z:
Next
End Sub

 

[Nashpotatoes]

Thank you both very much, both very useful answers, but I think the VBA option from bebo021999 is more what I am looking for, however I want to apply this to fairly large bits of text, that may have negative words associated with other phrases (i.e. not just relating to wear and tear), but I want to select out those that 1) mention a specific phrase e.g wear and tear 2) talk positively about it, i.e. suggest that it is present 3) include a third column that has 'borderline' or indeterminate phrasing 4) Ignores where these positive or negative descriptors are used to describe something else (e.g. the presence of dirt/markings)
Nb. point 3 is not essential but preferable

I am not sure if conditions like above can be placed on text within the same sentence that contains a specific phrase within a text field, or possibly within 20-40 characters before it... Apologies if this sentence doesn't make much sense

Thank you so much

 

[bebo021999]

With:
"2) talk positively about it"
to consider that is positive, i think excel can not.
like this: "I want to die", "What the hell!" , "... f*k..." how to know if it positive/negative
Try to tell excel that phrase with " no ", " not ", " non " ... is negative, else, positive. (like my code in #3)

 

[Dan_W]

however I want to apply this to fairly large bits of text, that may have negative words associated with other phrases (i.e. not just relating to wear and tear)

If it were just a matter of a few lines of VBA code, then the entire field of Machine Learning would cease to exist. Unless you're able to able to come up with rules that can cater for all manner of linguistic and syntactic variances, I think you may be underestimating the scale of the task. I agree with bebo021999 - Excel can't make those determinations for you (positive or negative), but it can identify specific words or phrases. Anything more than that would require either an extremely elaborate algorithm (that could still produce false positives/negatives) or, again, machine learning...

 

[6StringJazzer]

I want to select out those that 1) mention a specific phrase e.g wear and tear 2) talk positively about it, i.e. suggest that it is present 3) include a third column that has 'borderline' or indeterminate phrasing 4) Ignores where these positive or negative descriptors are used to describe something else (e.g. the presence of dirt/markings)

If you just need to recognize negative words, then @bebo021999 's code is pretty good. But if you need to actually infer meaning from the text, you need an AI solution that is much bigger and more complex than what I think you are willing to tackle.

 

[mikerickson]

"There was wear & tear, but no damage"

 

[Nashpotatoes]

With:
"2) talk positively about it"
to consider that is positive, i think excel can not.
like this: "I want to die", "What the hell!" , "... f*k..." how to know if it positive/negative
Try to tell excel that phrase with " no ", " not ", " non " ... is negative, else, positive. (like my code in #3)

Apologies, yes the code in #3 is excellent and exactly what I’m looking for! however the piece of text I’m looking in is about 10-15 sentences, and want to do exactly what your code does (highlight when there is ‘wear and tear’ without it being a negative) but simultaneously ignore these words when they are talking about something else. Wear and tear is an example, and they are in fact similar to medical reports like below, only smaller and probably more succinct:

I essentially want to differentiate between:
- there is no sign of X
- There is extensive Y
- There is some Z
- There is not any W
Within a small paragraph of text
And have a list of reports that show presence of X or Y or both

Thank you all very much for your help