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!
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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"


 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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"
 
Upvote 0
Try this:

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

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top