Results 1 to 7 of 7

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

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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_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!

  2. #2
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,258
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting multiple keywords from a cell

    Try this:


     AB
    1TextResult
    22016_00_InboundWeb_Request_A_DemoInboundWeb
    32016_04_PPC_Google_GenericPPC
    42016_00_InboundWeb_InboundCall&ChatInboundWeb
    52016_12_Telemarketing_Infocore_Spa-SalonTelemarketing

    Formeln der Tabelle
    ZelleFormel
    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"


    Regards Dante Amor

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting multiple keywords from a cell

    Quote Originally Posted by KK3008 View Post
    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.

  4. #4
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,258
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Extracting multiple keywords from a cell

    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.
    Regards Dante Amor

  5. #5
    New Member
    Join Date
    May 2019
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extracting multiple keywords from a cell

    Quote Originally Posted by KK3008 View Post
    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.

  6. #6
    Board Regular alansidman's Avatar
    Join Date
    Feb 2007
    Location
    Steamboat Springs
    Posts
    4,935
    Post Thanks / Like
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)

    Default 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"
    Let me know if that works for you
    Alan

    Am Yisrael Chai

    Win 10--Office 2019
    When Posting Code, please use code tags.
    How to insert Mcode to Power Query https://excel.solutions/2017/11/powe...te-code-video/


  7. #7
    Board Regular DanteAmor's Avatar
    Join Date
    Dec 2018
    Location
    México
    Posts
    6,258
    Post Thanks / Like
    Mentioned
    71 Post(s)
    Tagged
    14 Thread(s)

    Default 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)))
    Regards Dante Amor

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •