Pulling one or more words from string of text

[benross94]

Referring to the document linked here, you will see in column D, there is a formula successfully pulling which Truck # is mentioned in column B. However, it stops after the first Truck mentioned, even though sometimes there may be multiple Truck #s (row 7 for example). Can someone please tell me what formula I would need to have all Truck #s pulled out in separate cells?

[#2 · Accepted solution]

@benross94 

=LET(
a,TEXTSPLIT(B2,,", "),
b,ISNUMBER(SEARCH("Truck",a)),
c,FILTER(a,b),
d,SUBSTITUTE(c," Crew",""),
TRANSPOSE(d))

[#3]

So fast. So good. So grateful thank you!