Extracting multiple keywords from a cell

[KK3008]

I am trying to create a formula that will extract the campaign name that generated the lead in the sheet.

The campaign name column looks like this-
2016_00_InboundWeb_Request_A_Demo
2016_04_PPC_Google_Generic
2016_00_InboundWeb_InboundCall&Chat
2016_12_Telemarketing_Infocore_Spa-Salon

The output should look like this-
InboundWeb
PPC_Google

Any leads would be helpful!

 

[DanteAmor]

Try this:


<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:292.75px;" /><col style="width:172.99px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; text-align:center; ">Text</td><td style="font-weight:bold; text-align:center; ">Result</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">2016_00_InboundWeb_Request_A_Demo</td><td >InboundWeb</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">2016_04_PPC_Google_Generic</td><td >PPC</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">2016_00_InboundWeb_InboundCall&Chat</td><td >InboundWeb</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="color:#333333; font-family:Verdana; font-size:9pt; ">2016_12_Telemarketing_Infocore_Spa-Salon</td><td >Telemarketing</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formeln der Tabelle</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Zelle</td><td >Formel</td></tr><tr><td >B2</td><td >=TRIM(MID(SUBSTITUTE(A2,"_",REPT(" ",100)),200,50))</td></tr></table></td></tr></table>

Just one detail, how to know when to consider 2 words as in your example:: "PPC_Google"

 

[KK3008]

I am trying to create a formula that will extract the campaign name that generated the lead in the sheet.

The campaign name column looks like this-
2016_00_InboundWeb_Request_A_Demo
2016_04_PPC_Google_Generic
2016_00_InboundWeb_InboundCall&Chat
2016_12_Telemarketing_Infocore_Spa-Salon

The output should look like this-
InboundWeb
PPC_Google

Any leads would be helpful!

In this case, only campaign name "2016_04_PPC_Google_Generic" would require the output as "PPC_Google"- i.e. 2 words.

 

[DanteAmor]

Try this

=IFERROR(IF(FIND("PPC_Google",A2)>0,"PPC_Google"),TRIM(MID(SUBSTITUTE(A2,"_",REPT(" ",100)),200,50)))

If you have other campaigns that contain 2 names separated by an underscore, then we look for another solution.

 

[KK3008]

I am trying to create a formula that will extract the campaign name that generated the lead in the sheet.

The campaign name column looks like this-
2016_00_InboundWeb_Request_A_Demo
2016_04_PPC_Google_Generic
2016_00_InboundWeb_InboundCall&Chat
2016_12_Telemarketing_Infocore_Spa-Salon

The output should look like this-
InboundWeb
PPC_Google

Any leads would be helpful!

DanteAmor, thank you. Actually, there are other keywords where we have to pick to words. In fact, all the words starting with "PPC_" require 2 words to be extracted. For example- "PPC_Google", "PPC_Bing", "PPC_LinkedIn". We would require to write a generalized function for the PPC keywords.

 

[alansidman]

An alternative solution using Power Query/Get and Transform

Mcode:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", Int64.Type}, {"Column1.2", Int64.Type}, {"Column1.3", type text}, {"Column1.4", type text}, {"Column1.5", type text}, {"Column1.6", type text}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",{"Column1.5", "Column1.6"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Custom", each if([Column1.3])="PPC" then [Column1.3] & "_" & [Column1.4] else [Column1.3]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",{"Column1.1", "Column1.2", "Column1.3", "Column1.4"})
in
    #"Removed Columns1"

 

[DanteAmor]

Try this:

=IFERROR(MID(SUBSTITUTE(A2,"_",REPT(" ",99),4),FIND("PPC",A2),50),TRIM(MID(SUBSTITUTE(A2,"_",REPT(" ",100)),200,50)))