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

I

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)

[TABLE="width: 910"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD][TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD]Title[/TD]
[TD]ORG Name[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Key_Title[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]Key_ORG[/TD]
[TD]Final_Keyword[/TD]
[TD]Keyword Source[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Keywords_Title[/TD]
[TD]
[/TD]
[TD]Keywords_ORG[/TD]
[/TR]
[TR]
[TD]ACCOUNT MANAGER[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Private[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]Private[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Private[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]ORG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Doctor[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Hospital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]ACCOUNT MANAGER
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]GEMSPO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]DR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Clini[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]Pharmaceist[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Government[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Pharm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]GOV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Pharm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]found in Both table[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]dr.[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]GOV[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]ACCOUNT MANAGER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]GEMS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Radio[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Research[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]ENGINEERING MAMMO[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]HEALTHCARE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]Health[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Health[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ORG[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Prof[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Medic[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]MODALITY MANAGER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]GEMSR[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Hospital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Health[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]ACCOUNT MANAGER[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Clinical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]Clini[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Clini[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ORG[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Nurse[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Rad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]Professional[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]GEMS[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Prof[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Prof[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TITLE[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]Pharm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Pharm[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]SCE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Medical[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]Medic[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Medic[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]ORG[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 87"]
<tbody>[TR]
[TD="class: xl65, width: 87"]ANAESTH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Private[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]Hospitality[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]NULL[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 85"]
<tbody>[TR]
[TD="class: xl65, width: 85"]Hospital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Hospital[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]TITLE[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 193"]
<tbody>[TR]
[TD="class: xl65, width: 193"]SQE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 73"]
<tbody>[TR]
[TD="class: xl65, width: 73"]Radiology[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD][TABLE="width: 81"]
<tbody>[TR]
[TD="class: xl65, width: 81"]Rad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 83"]
<tbody>[TR]
[TD="class: xl65, width: 83"]Rad[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 132"]
<tbody>[TR]
[TD="class: xl65, width: 132"]ORG[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Appreciate your help. Thanks!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Rijnsent said:
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
Click to expand...


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
You must log in or register to reply here.

Similar threads

M
  • Question Question
ISO VBA Code / Other solutions Search Wkbk for text string and return data to single sheet
Replies
1
Views
460
Larry Haydn
Larry Haydn
A
  • Question Question
Help with existing code in Post - VBA to Create Org Chart
Replies
1
Views
2K
mrpotato562
M
M
  • Solved
Search row for keyword and build a new table with values
Replies
7
Views
649
DanteAmor
DanteAmor
V
  • Question Question
Search and return a match from a range with a string of text
Replies
3
Views
727
Peter_SSs
Peter_SSs
S
  • Question Question
Search a table and return rows based on criteria of 2 cells
Replies
1
Views
723
Fluff
Fluff

Forum statistics

Threads
1,224,820
Messages
6,181,154
Members
453,021
Latest member
Justyna P

Share this page

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