Search for a string and if found, select that row? [Solved/Closed]

Report
Posts
2
Registration date
Saturday January 25, 2014
Status
Member
Last seen
January 29, 2014
-
Posts
2
Registration date
Saturday January 25, 2014
Status
Member
Last seen
January 29, 2014
-
I know this is a very simple question but I can't find the answer I need. I am using Excel 2007 but I can get to a computer with Excel 2010 if necessary.

I have a spreadsheet with 42 columns and at least 5500 rows. In one column is a lot of text. I want to select all the rows that do not have a specific word in that column and delete them. If that's hard, then I could select all the rows that have that word and copy them into a new sheet.

Here's an example of what I want to do.

My table:

Name Dogs Fenced yard Dog types Oldest
Venus 3 Yes Poodle, Sheltie, Bulldog 15
Mars 2 Yes Shih Tsu, Yorkie, Poodle 12
Ares 2 No Yorkie, Great Dane 3
Zeus 3 Yes Rat Terrier, Border Collie, Saint Bernard 7
Hera 1 No Poodle 14

Goal: to keep all rows that have either poodl

I want to keep the rows that have the word poodle and move it to a new sheet. It would be even better if I could select the rows that had either the word poodle or the word yorkie. In that case I could just delete the others.

2 replies

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
in B1 type tis formula

=IF(AND(ISERROR(SEARCH("yorkie",A1)),ISERROR(SEARCH("poodle",A1))),"",A1)

copy B1 down and see what you get and you can modify this idea
download this file and see

http://speedy.sh/9yZ47/naybedog-140128.xlsx
Posts
2
Registration date
Saturday January 25, 2014
Status
Member
Last seen
January 29, 2014

Great, thank you! I modified the results to be "yes" or "no" rather than the content of the cells and that's perfect. Thank you so much!