Matching a field using multiple keyword

[CM100]

Hi Everyone, 

Appreciate some help with the following 

Am looking to conduct "if contain "String1 or String2", then "String" on the following example field on a formula tool 

Objective is the following 

- if Campaign column contains "(S)" and "Brand" then "Branded Paid Search"

Elseif 

- if Campaign column contains "(S)" only then "Generic Paid Search" 

Campaign

(S) Car Insurance_Brand_BMM

(S) Car Insurance_Brand_EM

(S) Car Insurance_Cheap_BMM

(S) Car Insurance_Cheap_EM

Was wondering if Regex is the best method to go about addressing this ? 

if it is what will the Regex be ? 

Thanks in advance !! 

[AndrewS · Accepted solution]

Hi,

Simple Formula with a couple of IFs should handle this.

IF
Contains([Campaign], '(S)') AND Contains([Campaign], 'Brand')THEN "Branded Paid Search"
ELSEIF Contains([Campaign], '(S)') THEN "Generic Paid Search"
ELSE 'NO RESULT' ENDIF

[lmorrell · Accepted solution]

Hi @CM100 

Solution is attached. 

This can be done with an ELSEIF statement containing two CONTAINS() functions, or with a similar logic but specifying the exact pattern via a REGEX_MATCH() function. 

In this example, an ELSEIF function would look like

if contains([Campaign], '(S)') AND contains([Campaign], 'Brand') then 'Branded Paid Search'
elseif Contains([Campaign], '(S)') then 'Generic Paid Search'
else [Campaign]
endif

and a REGEX_MATCH() would look like

if REGEX_Match([Campaign], '.*?\(S\).*?Brand.*') then 'Branded Paid Search'
elseif regex_match([Campaign], '^.*?\(S\).*') then 'Generic Paid Search'
else [Campaign]
endif

If you encounter performance issues, then I find that ELSEIF statements are a little quicker.

Hope this helps!

[CM100]

Awesome Andrew, 

Thanks for the solution. 

It works !! 

Btw i figured that you may have a more elegant way to do this 

Am trying to seperate (screenshot below) campaign named " car_insurance_brand" from "Brand Core"

Is there a way to establish a condition for "Contain..." And "Do Not Contain .. ."

[AndrewS · Accepted solution]

Hi @CM100 

To change a contains to a does not contain all you need to do is place a '!' before contains - as in

!Contains([dsCampaign], ...

[CM100]

Much Appreciated Imorrell ! 

[CM100]

Thanks Andrew, 

It works perfectly !