Extracting words from strings when string format differs (Google sheets)

Question

I am trying to extract campaign names from lines of strings. The problem is that campaign name formats are different.

• What would be the best way to extract campaign names in bold in this case?
• Is it possible to do this combining IF and TRIM functions?

Beginnings repeat in multiple campaigns:

• AO:11112_LTV_EDP_
• AO:12311_
• RTU_EDP_CHIKITA_
• AO:15616_FI_

Campaign list:

• AO:11112_LTV_EDP_BAMBINO_All_Q1BeesAreGettingThem_mumba_jumba_v23
• AO:12311_slimyinfluencer_v24_photoad
• RTU_EDP_CHIKITA_PlainSnail_ai_karamba_v2
• AO:15616_FI_potatogoestohollywood_hail_the_snail_0

In case of single format, I was using formula:

=TRIM(SPLIT((MID(A2,(SEARCH("EDP",A2)),LEN(A2))),"_"))

Thank you for your help!

Answer 1

If there's a limited number of campaigns you can make a list of all known campaign names and then extract them from longer string.

How to make a list. If you put in B2:

=regexextract(a2,"Q1BeesAreGettingThem|slimyinfluencer|Snail|potatogoestohollywood") - this will extract the name of campaign found.

If there's for example 50 campaign names and growing, you can make a column with your campaign names (let's say in column D)

Then you join all these names into one expression and put into regexectract:

=regexextract(a2,join("|",D2:D))