populating a separate column from data taken from another column based on "if contain the word" with multiple matches.

[pedexe90]

I have a range of vertical data in one column from E1:E1406 with cells containing 5-6 letters. I want to create a separate column next to it which would extract cells from E1:E1406 containing a certain word, in this case the word "FT" and populate the separate column automatically.

I tried Vlookup but that only finds the first cell containing the FT.

I've also tried =IF(SEARCH("FT",E1),E1,"". But, using the search formula would leave the unmatched cells empty. I simply want words with letters "FT" to be repopulated from one column to another column without any gaps between them (or empty cells. I think there is SMALL(IF()) formula that can be used or an index formula which could look up all the cells in one column and create another cell containing the matched letters.

Many thanks

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel...column-based-if-contain-word.html#post5052411

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

Note that sometimes posts from new users require Moderator approval before you can see them on the public forums. When this happens, you should see a message to that effect when you try to post it.
Please be patient and do not attempt to post the question again.