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

imhbk

New Member
Joined
May 16, 2019
Messages
5
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)

ABCDEFGHIJKL
TitleORG Name
Key_Title

<tbody>
</tbody>
Key_ORGFinal_KeywordKeyword Source


Keywords_Title
Keywords_ORG
ACCOUNT MANAGER
Private

<tbody>
</tbody>

Private

<tbody>
</tbody>
Private

<tbody>
</tbody>
ORG

<tbody>
</tbody>



Doctor

<tbody>
</tbody>

Hospital

<tbody>
</tbody>
ACCOUNT MANAGER
GEMSPO

<tbody>
</tbody>







DR

<tbody>
</tbody>

Clini

<tbody>
</tbody>
Pharmaceist

<tbody>
</tbody>
Government

<tbody>
</tbody>
Pharm

<tbody>
</tbody>
GOV

<tbody>
</tbody>
Pharm

<tbody>
</tbody>
found in Both table

<tbody>
</tbody>



dr.

<tbody>
</tbody>

GOV

<tbody>
</tbody>
ACCOUNT MANAGER

<tbody>
</tbody>
GEMS

<tbody>
</tbody>







Radio

<tbody>
</tbody>

Research

<tbody>
</tbody>
ENGINEERING MAMMO

<tbody>
</tbody>
HEALTHCARE

<tbody>
</tbody>

Health

<tbody>
</tbody>
Health

<tbody>
</tbody>
ORG


Prof

<tbody>
</tbody>

Medic

<tbody>
</tbody>
MODALITY MANAGER

<tbody>
</tbody>
GEMSR

<tbody>
</tbody>







Hospital

<tbody>
</tbody>

Health

<tbody>
</tbody>
ACCOUNT MANAGER

<tbody>
</tbody>
Clinical

<tbody>
</tbody>

Clini

<tbody>
</tbody>
Clini

<tbody>
</tbody>
ORG


Nurse

<tbody>
</tbody>

Rad

<tbody>
</tbody>
Professional

<tbody>
</tbody>
GEMS

<tbody>
</tbody>
Prof

<tbody>
</tbody>

Prof

<tbody>
</tbody>
TITLE


Pharm

<tbody>
</tbody>
Pharm

<tbody>
</tbody>
SCE

<tbody>
</tbody>
Medical

<tbody>
</tbody>

Medic

<tbody>
</tbody>
Medic

<tbody>
</tbody>
ORG


ANAESTH

<tbody>
</tbody>

Private

<tbody>
</tbody>
Hospitality

<tbody>
</tbody>
NULL

<tbody>
</tbody>
Hospital

<tbody>
</tbody>

Hospital

<tbody>
</tbody>
TITLE





SQE

<tbody>
</tbody>
Radiology

<tbody>
</tbody>

Rad

<tbody>
</tbody>
Rad

<tbody>
</tbody>
ORG

<tbody>
</tbody>







<tbody>
</tbody>


<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Appreciate your help. Thanks!
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,165
Office Version
365
Platform
Windows
Hi Imhbk,
try this solution, it works with an array formula (without macros): https://exceljet.net/formula/get-first-match-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
 

imhbk

New Member
Joined
May 16, 2019
Messages
5
Hi Imhbk,
try this solution, it works with an array formula (without macros): https://exceljet.net/formula/get-first-match-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...
 

Rijnsent

Well-known Member
Joined
Oct 17, 2005
Messages
1,165
Office Version
365
Platform
Windows
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
 

Forum statistics

Threads
1,085,169
Messages
5,382,116
Members
401,771
Latest member
Polarak

Some videos you may like

This Week's Hot Topics

Top