Search for a string and if found, select that row?

Solved/Closed
maybedog Posts 2 Registration date Saturday January 25, 2014 Status Member Last seen January 29, 2014 - Jan 26, 2014 at 04:01 AM
maybedog Posts 2 Registration date Saturday January 25, 2014 Status Member Last seen January 29, 2014 - Jan 29, 2014 at 12:50 AM
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.
Related:

2 replies

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 28, 2014 at 12:11 AM
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
0
maybedog Posts 2 Registration date Saturday January 25, 2014 Status Member Last seen January 29, 2014
Jan 29, 2014 at 12:50 AM
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!
0