Excel- Identify cells that contain specific text 1

[rdy4trvl]

Situation - I have a mailing list in Excel with approx. 7000 names. Some of the business names (home builders) I do not want to include (their name would include certain key words like "house, home, builders, developers, etc.). Is there an effecient way to search the names for those that contain certain of these key words>

I did use the search function, setting up a column for each key word search(house, home*, devel*, constru*) but it was rather combersome. Ideally, I would like a single search of the names for the key words and return "Builder" if any of the key words are found in a name. Any ideas?
Thanks
Doug

 

[SkipVought]

Doug,

I would use AutoFilter and the Contains operator to do a series of tests to identify (using a blank adjacent column) the rows that contain the unwanted values.

Then filter out all the above identified rows and you have a "clean" list.



Skip,

http://www.TheOfficeExperts.com

 

[SkipVought]

You could also list the strings you want to search for in row 1 to the right of your table.

Then use a formula like this (assuming that col A is the Builders and you table has 7000 rows and the strings begin in C1)

=IF(ISERROR(FIND(C$1,$A$2:$A$7000)),0,FIND(C$1,$A$2:$A$7000))

copy across and down

All positive numbers indicate a "hit"

Sum them across and filter on rows with 0.



Skip,

http://www.TheOfficeExperts.com

 

[rdy4trvl]

Thanks Skip
I was doing something like your second idea, but not as clean - so thanks for that. But I like the first idea given I havent used either command - good time to learn!
Doug

 

[SkipVought]

Believe me, I learn alot here. I am still learning

Skip,

http://www.TheOfficeExperts.com