Extract Text String within Cell, compare with list and return value [closed]

I have a string of text containing item names and artist titles etc.

I also have a seperate list of artist names.

I am trying to find out how to search the cell to match text from within the list, and then return the specific matched text.

As the data is from different sources the formats are different so splitting cells is messy, and I can only seem to return True or False or a specific set text.

As I need to understand what has been matched, how can I return that value?

Many thanks.

Solution 1:

With phrases in column A and artists names in column C from C1 through C6, in B1 enter the Array Formula:

=TEXTJOIN(",",TRUE,IF(ISNUMBER(SEARCH($C$1:$C$6,A1)),$C$1:$C$6,""))

and copy downward. For example:

As you see, the artist name is extracted. If there is more than one name in the phrase, then all are extracted.

Array formulas must be entered with Ctrl + Shift + Enter rather than just the Enter key. If this is done correctly, the formula will appear with curly braces around it in the Formula Bar.

NOTE:

You can adapt this to your own data schema.