In Excel how do I create a condensed list from another array?

1
votes

I have a long list of keywords in the A column. I am trying to search that column for cells that contain a specific word. I then want to display all cells that contain that word in a new column. For instance, if I want to search cells that contain "dog" anywhere within a cell between the range of A1:A2000 and then populate cells in the C column with the values of the cells that contained the word "dog" in it. In effect creating a new list

Thank you.

2
excelexcel-formula
quickest way is pivot table, did you considered it? – Máté Juhász

2 Answers

0
votes

Place in cell "C1" and copy down. Be sure and enter the formula with CTRL + SHIFT + ENTER:

=IFERROR(INDEX($A$1:$A$2000,SMALL(IFERROR(IF(SEARCH("dog",$A$1:$A$2000),ROW($A$1:$A$2000)-ROW($A$1)+1),FALSE),ROWS($C$1:C1))),"")
0
votes

Try below formular in C1 cell:

IF(ISNUMBER(SEARCH(A1,"dog")), A1, "")

Hope this can help