Extracting multiple keywords from a cell

KK3008

New Member
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

Well-known Member
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
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
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
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
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
Try this:

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

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top