Searching a field with a paragraph of text for a column containing keywords. Excel Help?

Question

Hi,

I have two columns, one containing keywords and one containing a specific value for that keyword.

Is there a way I can paste a paragraph of text into a field in excel (like a search box).. click a button and it would search that paragraph of text for any of the keywords in column A does and if it finds the same keyword in the paragraph and in Column A’s range.. it would display the B columns value for that Keyword?

Answer 1

This can easily be done in Excel using a VBA event handler. The following example assumes the paragraph will be pasted into cell C1.



Copy this event handler to the clipboard (highlight the entire code, right click inside the highlighted area, and 'Copy').



Private Sub Worksheet_Change(ByVal Target As Range)

Dim i, LastRow, kWrd

On Error Resume Next

LastRow = Range("A" & Rows.Count).End(xlUp).Row

If Target.Address(0, 0) <> "C1" Then

Exit Sub

Else

Range("BN1:XFD1").ClearContents

Range("C1").Copy Destination:=Range("BN1")

Application.CutCopyMode = False

Range("BN1").TextToColumns Destination:=Range("BN1"), _

DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _

ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, _

Comma:=True, Space:=True, Other:=False, FieldInfo:= _

Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1)), _

TrailingMinusNumbers:=True

For i = 1 To LastRow

For j = 66 To 566

If UCase(Cells(i, "A").Value) = UCase(Cells(1, j).Value) Then

kWrd = kWrd & Chr(10) & Cells(i, "A").Value & " " & Cells(i, "B").Value

End If

Next j

Next i

End If

If kWrd <> "" Then

MsgBox kWrd, vbOKOnly, "Match Found"

Else

MsgBox "No Match", vbOKOnly, "No Key Words"

End If

Range("BN1:XFD1").ClearContents

End Sub



Select the worksheet to containing the keywords and right click the sheet tab at the bottom.



Select 'View Code'.



Paste the event handler into the white editing area to the right (right click inside the area and 'Paste')



Close the VBE (red button - top right).



Save the workbook a a Microsoft Excel Macro-Enabled workbook to retain the VBA functionality when the workbook is opened in the future. Archive, or delete, the original version to prevent confusion as to which to use.



Copy and paste a paragraph into cell C1 and press ENTER. A message box will display containing all found keywords and their associated value.



Note: this process is not case sensitive. It will parse 'cat', 'CAT', 'Cat', and even 'CaT' as a match.

Answer 2

Hey,



Let’s say your list of keywords is in A1:A3 and are

Apple

Banana

Orange



Let’s say your B column has:

Red

Yellow

Orange



Let’s say if the keyword is mentioned you would like to be displayed in the respective row in the D column.



Finally let’s say your “search” box is C1.



In Cell D1 input the below formula:

=IF(ISNUMBER(SEARCH(A1,C1))=“TRUE”,B1,””)



The above is essentially saying if Apple (in A1) is mentioned in C1, then show Red (B1) in this cell.



If Apple is not mentioned then show the cell as blank.



I hope this works for you.



Best Wishes

Answer 3

There's no way I can see to do that in Excel 2010; but if they added something new lately you might find it under Paste>Paste special... in the Home ribbon.



What I usually do to get text into Excel columns is either:



-- Edit the file in Word, using the "Convert text to table..." dialog to make a Word table, then copy paste that table into Excel.



-- Edit the text file in a text editor (or Word) to convert spaces to commas and save the result as a .csv file. Excel will open that directly.



A third option I've used when there's a lot of table data and/or tricky editing that I might need to do more than once, is to write a Python program to do the line-by-line conversion.

Answer 4

I am not an excel expert.

What I would do is use a text to cell conversion for the paragraph. Then do a sort by alphabet. After which I would do my own identifying of the key words I was interested in.

I think the key is to convert the text to individual cells to do the search.