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!
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
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...
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,205
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top