Find word in a column from list of words and report word found

Jmrb2356 3 Posts Wednesday November 22, 2017Registration date November 23, 2017 Last seen - Nov 22, 2017 at 07:36 PM - Latest reply: Jmrb2356 3 Posts Wednesday November 22, 2017Registration date November 23, 2017 Last seen
- Nov 23, 2017 at 07:27 AM
Hello,

I have a large data set that I would like to search one column that contains sentences or purchases. I'd like to search from a group of words and have the word reported in an adjacent column. For example (see below), if the column contains flowers and supplies purchased can I search that column with a list of different flower names (i.e. begonia, sunflower, dandelion, lilies, roses, etc.) and whatever is found get that name reported in the next column. I don't have much background in this and I know it would need to be a part search and not a whole word search, but all I have seen are reports with True or False.

purchases results
25 red begonia begonia
3 bags of soil
5 yellow sunflower sunflower
2 flower pots
25 purple begonia begonia

Thanks in advance for the help!


See more 

4 replies

Reply to this topic
ac3mark 8362 Posts Monday June 3, 2013Registration dateModeratorStatus December 14, 2017 Last seen - Nov 22, 2017 at 08:13 PM
0
Helpful
2
I have some qualifying questions, please if I may?
Does the cell contain:
"25 red begonia begonia"

Or, does it contain:
A1=25
A2=red
A3=begonia
A4=begonia

This data set that you are referring to, would be much easier with a database, and a report! Just saying!
Jmrb2356 3 Posts Wednesday November 22, 2017Registration date November 23, 2017 Last seen - Nov 22, 2017 at 09:14 PM
The layout is D2="25 red begonia", D3="3 bags of soil", D4="5 yellow sunflower" and I would like the result to be in H2="begonia", H3="", H4="sunflower".

This is my first time using this, but I am not allowed to post the original document so I made one with the same layout but excluded the information that is not needed. The P column contains the list of flower names I'd like to use for the search. How do I upload excel document?
ac3mark 8362 Posts Monday June 3, 2013Registration dateModeratorStatus December 14, 2017 Last seen - Nov 22, 2017 at 10:00 PM
You do not upload the document. You upload a screenshot of the layout! There is a picture upload button.

Just to make certain, you want us to write code that will delimit the cell input, and place the delimited result in another cell?
Respond to ac3mark
Jmrb2356 3 Posts Wednesday November 22, 2017Registration date November 23, 2017 Last seen - Updated by Jmrb2356 on 23/11/17 at 07:30 AM
0
Helpful
No I know how to delimit. What I would like to do is search the purchases column using the keywords from the "List of Flowers". I have this code already but I didn't know if this was the right way to go about it since it was searching a column that only had this word in it. Also it places the answers in the E column where I would need it to be placed in the H column. I wanted to get other opinions on a better/easier way if possible.

Sub findflowers()
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("Sheet1")

For i = 1 To ws.Range("D65536").End(xlUp).Row
j = InStr(ws.Cells(i, 6).Value, "african lily")
If j <> 0 Then ws.Cells(i, 7).Value = "african lily"
j = InStr(ws.Cells(i, 6).Value, "alpine thistle")
If j <> 0 Then ws.Cells(i, 7).Value = "alpine thistle"
j = InStr(ws.Cells(i, 6).Value, "amazon lily")
If j <> 0 Then ws.Cells(i, 7).Value = "amazon lily"
j = InStr(ws.Cells(i, 6).Value, "baby's breath")
If j <> 0 Then ws.Cells(i, 7).Value = "baby's breath"
Next
End Sub

But this would be a lot of lines because I have a list of about 150 terms I'd like to search. And this code was if the only word in the cell were the names of the flowers and they aren't since there are amounts and colors in the same column.


Respond to Jmrb2356