# Thread: Extracting multiple keywords from a cell Thanks: 0 Likes: 0

1. ## Extracting multiple keywords from a cell

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_00_InboundWeb_InboundCall&Chat
2016_12_Telemarketing_Infocore_Spa-Salon

The output should look like this-
InboundWeb

2. ## Re: Extracting multiple keywords from a cell

Try this:

 A B 1 Text Result 2 2016_00_InboundWeb_Request_A_Demo InboundWeb 3 2016_04_PPC_Google_Generic PPC 4 2016_00_InboundWeb_InboundCall&Chat InboundWeb 5 2016_12_Telemarketing_Infocore_Spa-Salon Telemarketing

Formeln der Tabelle
 Zelle Formel B2 =TRIM(MID(SUBSTITUTE(A2,"_",REPT(" ",100)),200,50))

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

3. ## Re: Extracting multiple keywords from a cell

Originally Posted by 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_00_InboundWeb_InboundCall&Chat
2016_12_Telemarketing_Infocore_Spa-Salon

The output should look like this-
InboundWeb

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

4. ## Re: Extracting multiple keywords from a cell

Try this

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

5. ## Re: Extracting multiple keywords from a cell

Originally Posted by 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_00_InboundWeb_InboundCall&Chat
2016_12_Telemarketing_Infocore_Spa-Salon

The output should look like this-
InboundWeb

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.

6. ## Re: Extracting multiple keywords from a cell

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"```

7. ## Re: Extracting multiple keywords from a cell

Try this:

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