yogi_Pull First Artist Name From Artist String In Column A Before Occurrance Of Any Of The Specified Conjunctions

Google Spreadsheet   Post  #2491

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-18-2018

question by: Nick Fox SC
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/AO9f1CtJ4uk;context-place=mydiscussions
Extracting all words from a cell that occur before one of several given words
Hey everyone!

I'm trying to extract all words from a cell that occur before one of several given words. The use case for this is separating metadata to extract the first artist listed in a bunch of different complex artist strings. Below is my practical example:

Johnny Music 

Johnny Music featuring Timothy Muzak

Johnny Music, Timothy Muzak, Kevin Current

Johnny Music & Timothy Muzak feat. Kevin Current

Johnny Music feat. Kevin Current & Timothy Muzak

Johnny Music remixed by Kevin Current

In all cases, the result I want is 'Johnny Music'. 

I've tried a couple of LEFT, TRIM, and MID commands but they aren't letting me search for multiple different places to stop. For example:

=MID(A1;1;MIN(SEARCH("remixed by";A1&"featuring");SEARCH("remixed by";A1&"featuring"))-1)

=LEFT(A1;SEARCH({"remixed by";"&";"featuring"};A1)-1)

Both of these failed to SEARCH beyond 'remixed by', so they only extracted 'Johnny Music' in the last case, not in the other cases (featuring, etc were not located by the search. I got a '#VALUE!' error). 

Can anyone advise? Is there something obvious I am missing, or is this more complex than I first envisaged?

NOTE: This would be applied across multiple artist strings, so it's not as if all of them would start with 'Johnny Music' - therefore a simple LEFT with value parameters won't suffice.

Cheers everyone!