Excel: search for information

Question

Guys I need to find specific information in text, and write them down in column.

So, I have Column L with Long Description, and I have Column M with words that I need to find in Long Description. When word has been found write that word in Column N at same row as Long Description. I tried coding this one but ain't work.

=INDEX(M1:M4;MAX(IF(ISERROR(FIND(M1:M4;L1));-1,1)*(ROW(M1:M4)-ROW(M1)+1)))

This is sample of what I mean.. Pleaaase really need help.

Answer 1

This UDF should do the trick - I won't provide a detailed description of what it does beyond the comments you can see in it, as it's not very complex piece of code, but if there is something you don't understand in it, feel free to ask.

Option Explicit

Function find_keywords(cell_to_search As Range, range_of_keywords As Range) As String
  Dim c As Range

  ' Check for the value of each cell in the range of keywords passed to the function
  For Each c In range_of_keywords

    ' If the string-value we search for is in the cell we check against, we add it to the return-value
    If InStr(1, cell_to_search.Text, c.Text, vbTextCompare) > 0 Then

      ' We don't want a comma before the first keyword we add, so check for the length of the return value
      If Len(find_keywords) > 0 Then
        find_keywords = find_keywords & ", "
      End If

      find_keywords = find_keywords & c.Text
    End If

  Next

End Function

You need to paste the code above into a module in your workbook, and then enter the formula into the cell you want the return-value to as normal: