Formula to find words in list of text data

[metsmaniac]

I have a long list of rows of text data, each about 30 characters long.

I want to create a formula that finds words in the data from a separate list of words. The "find" function searches the text list one word at a time. I'm trying to search the list using a list of all the words I'm looking for.

I'd rather use a compound formula than Visual Basic.

For example, my word list could be color names:
Red
Blue
Yellow
Green
Etc.

I want to search the data list & identify rows that contain any of the color names.

 

[awoohaw]

Here is a simple answer. I'm not sure if your task is more complex:

Book1
	A	B	C	D
1	List		Word to Find	Word Found
2			Blue	TRUE
3	Red
4	Blue
5	Yellow
6	Green
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ISNUMBER(MATCH(C2,A3:A6,0))

 

[Drrellik]

When you say fine, a word in a data set or all of the words in one cell?
Or are they a long list in one column?

And when you say find, do you simply want to highlight the words or pull some data from another column associated with that word?

 

[metsmaniac]

I want to identify the text row that contains any of the words in the separate list.

For example, if a row in the data list reads "sfkldjfksj Red 5809485098", and "Red" is one of the colors in a separate list of colors, I want to somehow identify the row in the data list that contains the word "Red".

 

[awoohaw]

try this, formula in D8:

this just says its there somewhere. Knowing your excel version will help with a solution for WHERE.
Please update your profile so the forum doesn't have to ask.

Book1
	A	B	C	D	E	F
1	List		Word to Find	Word Found
2			Blue	TRUE
3	Red
4	Blue
5	Yellow		Find Word In Part of cell
6	Green
7	Red White		Word to find	Word Found
8	Red White and Blue		Blue	TRUE
9	Blue and Red		(May need to enter with CSE keystroke)
10	Yellow is not Green
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ISNUMBER(MATCH(C2,A3:A10,0))
D8	D8	=SUM(--(ISNUMBER(FIND(C8,$A$3:$A$10))))>0
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[awoohaw]

Here is a formula that may work. You must have a version that works with array formulas (just like the last one) and input using the CSE key stroke.
It may be fickle as it uses ROW and ROWS functions to and results may change if you move your formulas and data around.

Book1
	A	B	C	D	E
1	List		Word to Find	Word Found
2			Blue	TRUE
3	Red
4	Blue
5	Yellow		Find Word In Part of cell
6	Green
7	Red White		Word to find	Word Found
8	Red White and Blue		Blue	TRUE
9	Blue and Red		(May need to enter with CSE keystroke)
10	Yellow is not Green
11
12			Word to find	First Text Found
13			Blue	Blue
Sheet1

Cell Formulas
Range	Formula
D2	D2	=ISNUMBER(MATCH(C2,A3:A10,0))
D8	D8	=SUM(--(ISNUMBER(FIND(C8,$A$3:$A$10))))>0
D13	D13	=INDEX($A$3:$A$10,1/LARGE(IFERROR(1/(ISNUMBER(FIND(C8,$A$3:$A$10))*(ROW($A$3:$A$10)-ROWS(D13:D14))),0),1))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[metsmaniac]

Thanks for the responses.

I use Microsoft Office 365, and I've updated my profile with that info.

To clarify, I need a formula in column Q that returns "Yes" if ANY of the words in column S are within each row of column P. Otherwise, the formula returns "No".
I've provided an example below:

 

[AhoyNC]

See this link and answer from Peter_SSs

Keyword lookup from a list of keywords in a column of text descriptions

I have an Excel database of invoice data with a text column for the description of the work performed. I need to identify (T/F in a column?) any invoice that has any keywords from a list in the description column. I have not yet created the list of keywords as I am not sure how they should be...

www.mrexcel.com

Book4
	A	B	C	D
1
2	Data	In List		Find
3	Red and Qrange	Yes		Red
4	Red and Violet	Yes		Orange
5	Blue and car	Yes		Yellow
6	City and State	No		Green
7	Purple and Aqua	Yes		Blue
8	Redo this color	No		Purple
9	Orange and Dog	Yes		Black
10	Dog	No		White
Sheet1

Cell Formulas
Range	Formula
B3:B10	B3	=IF(COUNT(SEARCH(" "&$D$3:$D$10&" "," "&A3&" "))>0,"Yes","No")

 

[metsmaniac]

Brilliant! It worked!

Thank you!

 

[AhoyNC]

You're welcome. Have to give most of the credit to Peter. Thanks for the feedback.