Search a column for a list of terms, and return the term(s) found

[Gundy21]

I'm trying to search a column, for a list of terms, and have any matches listed in the new column. For example, the column I want to search has a mash-up of text "AAAAAAABOBAAAAAAJOHNAAAAAAASUEAAAAA" and I want to find out which of my list of names are in the text. The list of names is {BOB, CHAD, SUE, MIKE, DON} and the result needs to be "BOB, SUE". 

[Vijay_A_Verma · Accepted solution]

In a custom column, use following formula where [Text] is your column name and ListOfNames contains the names

Text.Combine(List.Select(ListOfNames, (x)=>Text.Contains([Text], x)), ", ")

[Gundy21]

Worked like a charm! Thanks @Vijay_A_Verma