 SOLVED

New Contributor

# Formula does not work for all

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?

7 Replies

# Re: Formula does not work for all

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) # Re: Formula does not work for all

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

# Re: Formula does not work for all

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.

# Re: Formula does not work for all

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

best response confirmed by Nruti Desai (New Contributor)
Solution

# Re: Formula does not work for all

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
```

# Re: Formula does not work for all

Thank you so much Matt.

It worked.

Thanks.

# Re: Formula works well

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