Search code examples
excelexcel-formulaworksheet-functionarray-formulas

How can I gather a list of cell contain a certain text

I need to search my entire worksheet a certain word and fill a column (B-in this case) contain the same word from column A without that specific word (in this case the word Photo). For example:

        A                 B
1   Photo Leon      |   Leon
2   Video Raul      |   Kelie
3   Broadcast Noa   |
4   Photo Kelie     | 
5   Video Carl      |
6   Broadcast Eitan |

Can anyone help?

Solution

  • Please try (adjust A$1:A$100 to suit): 

    =SUBSTITUTE(IFERROR(INDEX(A$1:A$100,SMALL(IF(LEFT(A$1:A$100,6)="Photo ",ROW(A$1:A$100)),ROW(B1))),""),"Photo ","")

    entered with Ctrl+Shift+Enter and copied down until the first blank cell appears.

    You might want to replace Photo with a parameter. Say enter Photo in D1 and instead: 

    =SUBSTITUTE(IFERROR(INDEX(A$1:A$100,SMALL(IF(LEFT(A$1:A$100,LEN(D$1))=D$1,ROW(A$1:A$100)),ROW(B1))),""),D$1,"")