Results 1 to 4 of 4

Thread: Search for multiple text strings simultaneously in a single cell through VBA code
Thanks Thanks: 0 Likes Likes: 0

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

    Default Search for multiple text strings simultaneously in a single cell through VBA code

    Hi,

    i have a sheet where i need to find the keywords from a list of keywords in each cell into a particular column.

    What I want to do, though, if look for a long list of keywords simultaneously, and if ANY of those keywords are present, have it return that "keyword".
    So what I want it to do is search each cell by simultaneous reference to an dynamic table of keywords (call it [TITLE & ORG]).

    To reiterate: the goal is to look at one cell filled with text, ask "does the text in that cell contain any of the keywords contained in [Keywords Table]", and if the answer is "Yes" return that keyword, and if the answer is no return Blank.

    Below is the sheet for reference and details
    Column A - Data
    Column B - Data
    Column C - search the keyword from list of keywords "Column J" in "column A" and return that keyword in "Column C" from Table (keywords_Title) "Column J"
    Column D - search the keyword from list of keywords "Column L" in "column B" and return that keyword in "Column D" from Table (Keywords_ORG) "Column L"
    Column E - return the keyword found in column C and D for example =IF(C2 = "",D2,C2)
    Column F - return the name of keyword source Table (Table Name) for example IF(AND(C2<>"",D2=""),"TITLE",IF(AND(D2<>"",C2=""),"ORG",IF(AND(D2<>"",C2<>""),"Both","")))

    Column J - Keyword Table (which will work with column A)
    Column L - Keyword Table (which will work with column B)

    A B C D E F G H I J K L
    Title ORG Name
    Key_Title
    Key_ORG Final_Keyword Keyword Source


    Keywords_Title
    Keywords_ORG
    ACCOUNT MANAGER
    Private

    Private
    Private
    ORG



    Doctor

    Hospital
    ACCOUNT MANAGER
    GEMSPO







    DR

    Clini
    Pharmaceist
    Government
    Pharm
    GOV
    Pharm
    found in Both table



    dr.

    GOV
    ACCOUNT MANAGER
    GEMS







    Radio

    Research
    ENGINEERING MAMMO
    HEALTHCARE

    Health
    Health
    ORG


    Prof

    Medic
    MODALITY MANAGER
    GEMSR







    Hospital

    Health
    ACCOUNT MANAGER
    Clinical

    Clini
    Clini
    ORG


    Nurse

    Rad
    Professional
    GEMS
    Prof

    Prof
    TITLE


    Pharm
    Pharm
    SCE
    Medical

    Medic
    Medic
    ORG


    ANAESTH

    Private
    Hospitality
    NULL
    Hospital

    Hospital
    TITLE





    SQE
    Radiology

    Rad
    Rad
    ORG







    Appreciate your help. Thanks!

  2. #2
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    997
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell through VBA code

    Hi Imhbk,
    try this solution, it works with an array formula (without macros): https://exceljet.net/formula/get-fir...-cell-contains
    Your formula for C5 (the cell with result "Pharma"): =IFERROR(INDEX($J$3:$J$11,MATCH(TRUE,ISNUMBER(SEARCH($J$3:$J$11,A5)),0)),"") -> CTRL+SHIFT+ENTER to make it an array formula.
    Cheers,
    Koen
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

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

    Default Re: Search for multiple text strings simultaneously in a single cell through VBA code

    Quote Originally Posted by Rijnsent View Post
    Hi Imhbk,
    try this solution, it works with an array formula (without macros): https://exceljet.net/formula/get-fir...-cell-contains
    Your formula for C5 (the cell with result "Pharma"): =IFERROR(INDEX($J$3:$J$11,MATCH(TRUE,ISNUMBER(SEARCH($J$3:$J$11,A5)),0)),"") -> CTRL+SHIFT+ENTER to make it an array formula.
    Cheers,
    Koen

    I already done with the formula and applied to my file. but i want to find the way to do this in a VBA. I think someone can help here...

  4. #4
    Board Regular Rijnsent's Avatar
    Join Date
    Oct 2005
    Location
    Utrecht, Holland
    Posts
    997
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Search for multiple text strings simultaneously in a single cell through VBA code

    Hi Imhbk,
    are you looking for a UDF (VBA built formula) or VBA code that you can run? What did you create yourself (please post code here in [ CODE ] brackets)?
    Thanks
    You can't post attachments here, but you can help me helping you by posting a screen shot directly in your post with any of those tools.
    Otherwise use dropbox/google drive/etc to get your file accross (not preferred). For code, put it inside these tags: [ CODE][/CODE]. Do check the forum rules.
    Finally, please show that you made an effort to solve your problem: Yes, I like to help, but am not going to do your job.

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
  •