Seeking Formula to separate cells that contain specific terms

[syncguy]

I have thousands of data entries (each in their own row, all in one column) and need to separate the basic info. In the example below, say you want to isolate the Town names by entering a formula that can produce a TRUE or FALSE result. Is there a way to use the FIND function and a pool of terms to isolate cells that contain excess data (i.e.- produce a FALSE result for cells containing any words from a term pool like : "Police, Park, Power, Water, Community, Bakery, Grocery, Field, Garden, Post Office, School" etc... ) ?

Pic of sample spreadsheet with desired result:
Screen Shot 2014-03-18 at 3.14.09 AM.png


example as text:
A2 = Springfield
A3 = Springfield Police Department
A4 = Springfield Community College
A5 = Springfield Park Of Recreation
A6 = Des Moines
A7 = Des Moines Power & Water Company
A8 = Des Moines Sunrise Bakery
A9 = Des Moines Post Office
A10 = Des Moines Grocery Store
A11 = Lancaster
A12 = Lancaster Baseball Field
A13 = Lancaster Botanical Gardens

[AZ-XL]

Hi
Solution depends on presented data actually. Formula below presents desired results for those informations
=IF((LEN(TRIM(A1))-IFERROR(LEN(SUBSTITUTE(TRIM(A1)," ","")),LEN(TRIM(A1))))>1,FALSE,TRUE)

[Tony Valko]

Here are the results I get:

Data Range

	A	B	C	D
1			------
2	Springfield	TRUE		Police
3	Springfield Police Department	FALSE		Park
4	Springfield Community College	FALSE		Power
5	Springfield Park Of Recreation	FALSE		Water
6	Des Moines	TRUE		Community
7	Des Moines Power & Water Company	FALSE		Bakery
8	Des Moines Sunrise Bakery	FALSE		Grocery
9	Des Moines Post Office	FALSE		Field
10	Des Moines Grocery Store	FALSE		Garden
11	Lancaster	TRUE		Post
12	Lancaster Baseball Field	FALSE		Office
13	Lancaster Botanical Gardens	TRUE		School

In A13 the word "gardens" is not an exact match (spelling-wise) with the keyword "garden".

The words have to be exact matches of the keywords. If there are variations like that above then you need to include those variations in the keyword list and they have to be listed in a specific order. Like this:

Data Range

	A	B	C	D
1			------
2	Springfield	TRUE		Police
3	Springfield Police Department	FALSE		Park
4	Springfield Community College	FALSE		Power
5	Springfield Park Of Recreation	FALSE		Water
6	Des Moines	TRUE		Community
7	Des Moines Power & Water Company	FALSE		Bakery
8	Des Moines Sunrise Bakery	FALSE		Grocery
9	Des Moines Post Office	FALSE		Field
10	Des Moines Grocery Store	FALSE		Gardens
11	Lancaster	TRUE		Garden
12	Lancaster Baseball Field	FALSE		Post
13	Lancaster Botanical Gardens	FALSE		Office
14				School

This formula entered in B2 and copied down:

=ISERROR(LOOKUP(1E+100,SEARCH(" "&D$2:D$14&" "," "&A2&" ")))

[AZ-XL]

Another solution.
=IF(COUNTIF(A:A,"*"&TRIM(A1)&"*")>1,TRUE,FALSE)
While testing if it gives wrong result then test your text (Des Moines and lancaster)

[AlKey]

Or this:

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))<=1

	A	B
1	Data
2	Springfield	TRUE
3	Springfield Police Department	FALSE
4	Springfield Community College	FALSE
5	Springfield Park Of Recreation	FALSE
6	Des Moines	TRUE
7	Des Moines Power & Water Company	FALSE
8	Des Moines Sunrise Bakery	FALSE
9	Des Moines Post Office	FALSE
10	Des Moines Grocery Store	FALSE
11	Lancaster	TRUE
12	Lancaster Baseball Field	FALSE
13	Lancaster Botanical Gardens	FALSE

[Tony Valko]

I must not understand what the OP wants.

My suggestion is nothing like the other suggestions.

[syncguy]

Thank you everyone! AZ-XL's formula worked best.. at least when I applied it to the bulk data. Still kind of baffled as to how these work with out the pool of terms that I was mentioning.. ? Amazing stuff

Another solution.
=IF(COUNTIF(A:A,"*"&TRIM(A1)&"*")>1,TRUE,FALSE)
While testing if it gives wrong result then test your text (Des Moines and lancaster)

[AZ-XL]

You are welcome. And thank you for feedback.

[AlKey]

Thanks for the feedback!