REGEX_Match function to match subset

[kavithabanda]

Hello!

I'm trying to resolve the following case:

I have to find a match in a list and the example is:

List of words:

------------------

PEPPERCORN

CORN MEAL

CORN OIL

CORN

The pattern match word is "CORN" so my result should return CORN MEAL, CORN OIL, CORN but NOT PEPPERCORN.

I built a sample workflow that uses both REGEX tool and a formula that uses REGEX_Match. REGEX tool returns correct results but I am not sure how to pass a dynamic string there instead of hard coding my pattern.

REGEX_Match does not match CORN MEAL, CORN OIL. It matches CORN with CORN only. How can I make this function work for 

CORN MEAL, CORN OIL (but not PEPPERCORN)?

I'm more leaning towards using REGEX_Match solution.

I'm attaching my workflow.

Thanks in advance!

K.

[ddiesel · Accepted solution]

Hi @kavithabanda

Do you have a list of words that you're trying to match? If so, I think a Find Replace tool will work for you. In the configuration, choose "Any Part of Field" and check the box for "Match Whole Word Only". You can make case sensitive/insensitive depending on your data. Then choose "Append Field(s) to Record:" instead of "Replace". I added "Peanuts" as an example.

See attached and let me know if this works!

[kavithabanda]

Thanks ddiesel, looks like this solution might work for me. I have to put this back into my original gigantic workflow to see how it plays there but this is what I was after. 

[kavithabanda]

@ddiesel,

This solution works separately but isn't working when I inject into the larger workflow. I am back to looking a solution using REGEX_Match. Any ideas?

[derekbelyea]

Have you tried using the FUZZY MATCH tool?

[kavithabanda]

No, Let me check that out.

[ddiesel]

Hi @kavithabanda 

If Find Replace and Fuzzy Match don't work, can you provide a larger sample of the data you're trying to match? What's not working about it?

There is a solution somewhere!

[ddiesel]

@kavithabanda

Check out this post:

Find-exact-matches-from-multiple-fields-using-batch-macro

@knozawa wrote a macro that I think solves for your use case.

Let us know if it works for you!

[Fruitlord]

Instead of "Corn" ... Try "Corn.*" or ".*Corn" depending on whether your string normally starts with corn

[Saumil]

Hi @ddiesel:

I am trying to do the exact same thing that you did using Find Replace tool. I have a list of words that I am trying to match from a column of text descriptions. The only problem that I am facing is that if the text contains more than one word from the list of words I want to match, it only returns the first one in the match column and not the remaining. 

For example: If I have a field with text "CORN PEANUT BUTTER" I want to return CORN and PEANUT both as output in the matched column and not just CORN. Is there a way I could do this ? Thanks in advance.