Formula does not work for all

[Nruti Desai]

I am using =IF(ISNUMBER(SEARCH("RangeName",G2)),RangeName) to find words in a cell for list of words. Once that word is found I want that word to be the result if the words nor found than say FALSE. I used this formula in 7,000 cells only 1 cell came back with correct answer. 

Why is that? 

[#2]

Try using a formula like the below.  I have attached an example file for additional reference:

=IF(ISERROR(SEARCH($C$2,F2)),"FALSE",$C$2)

[#3]

Thank you Matt for the quick response. 

I have list of 2000 words that I am looking for in the 7000 cell that contains comments. (min 20 word count per cell). 

If i use the =IF(OR(ISNUMBER(SEARCH("nice",A10)),ISNUMBER(SEARCH("great",A10)),ISNUMBER(SEARCH("thank",A10))),"yes","no") this works perfectly but only for selected words. 

What I want is a formula that will read comments in 7000 cells and look for any of the 2000 word list that I have. Once it finds that word I wanted it to tell me each words it found in each comments. 

Thanks 

[#4]

Would a VBA Code solution be acceptable?  I can't think of an easy way to do this otherwise.  If this is acceptable then would it be possible for you to give me a small set of non-sensitive data that I can test the code on?

Typically, my coworkers use text analytics/ sentiment analysis packages for statistical software to do things of this nature.

[#5]

Thank you Matt for your reply. 

Unfortunately I am unable to share any data with you. 

I am doing a sentimental analysis and trying to pick up positive or negative words and than score each words. I am unable to find a solution that will help me do this in excel.  

Thanks 

[#6 · Accepted solution]

Try using the below VBA Code to get the result you would like.  I'm attaching a .xlsx workbook as an example since it's not possible to attach macro enabled workbooks.  You will need to insert the code I have provided below into the workbook to see how it works.  Once you grasp the concept you can alter the code to work for your needs.  Hope this helps.

Before:

After:

Code:

Sub GetWords()

    Dim wrdLRow As Integer
    Dim wrdLp As Integer
    Dim CommentLrow As Integer
    Dim CommentLp As Integer
    Dim fndWord As Integer
    Dim Sht As Worksheet
    
    On Error Resume Next 'Suppress Errors... for when we don't find a match
    
    'Define worksheet that has data on it....
    Set Sht = Sheets("Sheet1")
    
    'Get last row for words based on column A
    wrdLRow = Sht.Cells(Rows.Count, "A").End(xlUp).Row
    
    'Get last row for comments based on column C
    CommentLrow = Sht.Cells(Rows.Count, "C").End(xlUp).Row
    
    'Loop through lists and find matches....
    For CommentLp = 2 To CommentLrow
    
        For wrdLp = 2 To wrdLRow

            'Look for word...
            fndWord = Application.WorksheetFunction.Search(Sht.Cells(wrdLp, "A"), Sht.Cells(CommentLp, "C"))
            
            'If we found the word....then
            If fndWord > 0 Then
                Sht.Cells(CommentLp, "D") = Sht.Cells(CommentLp, "D") & "; " & Sht.Cells(wrdLp, "A")
                fndWord = 0 'Reset Variable for next loop
            End If
            
        Next wrdLp
        
        Sht.Cells(CommentLp, "D") = Mid(Sht.Cells(CommentLp, "D"), 3, Len(Sht.Cells(CommentLp, "D")) - 2)

    Next CommentLp

End Sub

[#7]

Thank you so much Matt.

It worked. 

Thanks.

[#8]

@Matt Mickle Is it possible to extract the whole sentence containing word listed in column A rather than just a word.