Search code examples
regexgoogle-sheetsregex-group

Google sheets Regex function help needed

I have a DB of 600 talks on various topics by various speakers. They are in the format of:

Topic 1 (Speaker 1)

Event_type_1 - , Topic 2 (Speaker 2)

Event_type_2 - , Topic 3 (Speaker 3)

Topic 4 (subtopic 1) (Speaker 1)

Ideally, I would want to convert the DB to the format of:

Topic 1

Topic 2

Topic 3

Topic 4 (subtopic 1)

Something like =REGEXEXTRACT(Cell_1,"(.+)$") would extract the text within brackets but then it would extract all brackets. Combining with IF for only (Speaker 1, Speaker 2, ... , Speaker N) is possible but clumsy. Similarly, and IF group at the beginning for the (event_type_1, event_type_2, ... , event_type_N) is also possible but clumsy.

Feels like regex is powerful enough to enable me to do it cleaner but I'm lacking the skill. Would appreciate help and pointers!

Solution

  • You can use

    ([^,()\s][^,]*)\s\(

    See the regex demo.

    Details:

    • ([^,()\s][^,]*) - Group 1:
      • [^,()\s] - a char other than ,, ( and )
      • [^,]* - zero or more chars other than ,
    • \s - a whitespace
    • \( - a ( char.