Searching For Multiple Keywords Within Rows of Text

I have a dataset with ~300k rows that has a "Job Description" field which contains free form text entries describing construction projects. I need to parse the whole database to find observations that contain references to Ceiling, Drywall, Insulation, Hardware, Flooring, and Concrete. I need to use wildcards to allow for misspellings. I've figured out how to make it do what I need it to do for ONE keyword, but I don't know enough about RegEx to know how to make it use a logical OR to look for more than one keyword at a time per cell.

Here's what I've got. Each one of these lines is using the formula tool and creating a new output column:

Contains(('ceiling'), [Text])

iif(REGEX_Match([Text], '.*ceil.*'),'Ceiling'," ")

REGEX_CountMatches([Text],'Ceiling')

How can I modify this to search for more than one term at a time? Thanks in advance!

Regex

Parse

Accepted answers

andre347

What about using a lookup table and the Find Replace tool? This means you only have to update the lookup table if you want to look for new words rather than having to maintain a formula.

I mocked up a very simple solution (v11.7 attached).

Screen Shot 2017-12-01 at 11.07.33.png

Find Replace.yxmd

All comments

LordNeilLord

Hi,

For your Contains formula you can use Contains('ceiling', [Text]) OR Contains('drywall', [Text]) OR Contains('insulation', [Text]) etc..

For the RegEx formulas you can create a capturing group for example: REGEX_CountMatches([Product Name], "(Xerox|&|Black)")

andre347

What about using a lookup table and the Find Replace tool? This means you only have to update the lookup table if you want to look for new words rather than having to maintain a formula.

I mocked up a very simple solution (v11.7 attached).

Screen Shot 2017-12-01 at 11.07.33.png

Find Replace.yxmd

nspare

I like this solution, because it's the most reusable. The only problem is that sometimes the text entries might have multiple keywords, and this solution only returns the first keyword in the text string. I would need to either have a count column that is updated each time a keyword is found, or I would need to have each additional keyword generate a new column to show secondary or tertiary segments. Any thoughts? Thanks so much for your help!

nspare

Thanks for your help, now I know!

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703