Extracting multiple keywords from a cell

KK3008

New Member
Joined
May 8, 2019
Messages
8
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!
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
11,178
Office Version
2007
Platform
Windows
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

New Member
Joined
May 8, 2019
Messages
8
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,178
Office Version
2007
Platform
Windows
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

New Member
Joined
May 8, 2019
Messages
8
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

Well-known Member
Joined
Feb 26, 2007
Messages
5,432
Office Version
2019
Platform
Windows
An alternative solution using Power Query/Get and Transform

Mcode:

Code:
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

Well-known Member
Joined
Dec 3, 2018
Messages
11,178
Office Version
2007
Platform
Windows
Try this:

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

Watch MrExcel Video

Forum statistics

Threads
1,095,803
Messages
5,446,560
Members
405,407
Latest member
apat

This Week's Hot Topics

Top