Search for Keywords in a column

[Masond3]

Afternoon Team, 

Hoping you can help , or point me in the right direction 

This question is regarding a workflow that will allow me to search for keywords within a name column.

For this example, the purpose will be to search famous science quotes for a select group of keywords.

In my Raw input File i have the following 2 columns ;

ID

Name

Example Input 

ID	Name
001f100001Inn9NAAR	401K Mason
0013a00001k5p1iAAA	Insurance LTD
001f100001OiuyrAAB	Pension Plan.

Step 1 - i would like to have a reference file / table which contains key words

Keyword

401k

Pension

Life

Science

Step 2 - i would then like to search for these words in the reference file / table against the Name in the Input file 

Step 3 - Outcome column to indicate where we matched and if possible the weighting of percentage we matched on

ID	Name	Outcome
001f100001Inn9NAAR	401K Mason	Match
0013a00001k5p1iAAA	Insurance LTD	No Match
001f100001OiuyrAAB	Pension Plan.	Match

I feel that i we should use the Multi-Field Formula tool to change all words in both the text strings and keywords to uppercase and maybe include the the Use the RegEx 

Looking forward to your help 

Regards

[christine_assaad]

Hi @Masond3 

Check this out and see if it will do the trick for you. Cheers!

[Qiu]

@Masond3 
Tried a different approach if Find and Replace is not your favorite😁

I also considered the case that more than 1 keywords are matched.

I

[DevDingreja]

Hi Mason, this is great by any chance do you know of a way to also highlight or identify the keywords?

we currently do this manually and are dealing with huge amounts of data about 20k line items and 300 keywords, each line item sometimes has a summary of about 500 to 600 words. 

as of today, we do this via excel using the =COUNTIF(TextH:H, C3) where C3 would be the keyword e.g. *God*