May 25 2018
06:46 AM
- last edited on
Jul 31 2018
08:15 AM
by
TechCommunityAP
May 25 2018
06:46 AM
- last edited on
Jul 31 2018
08:15 AM
by
TechCommunityAP
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?
May 25 2018 07:13 AM
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)
May 25 2018 07:21 AM
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
May 25 2018 08:17 AM
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.
May 25 2018 08:22 AM
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
May 25 2018 10:17 AM
SolutionTry 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
May 14 2021 05:58 AM
@Matt Mickle Is it possible to extract the whole sentence containing word listed in column A rather than just a word.
May 25 2018 10:17 AM
SolutionTry 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