Replace String Before and After Variable Number of Delimiters

I have the following 2 fields. Field1 is populating with a list of fruits separated by a pipe. The list for each row could be variable length. Field2 should be the same length as Field1 but replace whatever specific fruit is listed with just "Fruit".

Right now I am solving by having multiple REGEX_CountMatches formulas for the various possibilities (IF REGEX_CountMatches([Field1], '\|')=3 THEN "Fruit|Fruit|Fruit|Fruit...), and it works fine, but would like to make it more dynamic since the list could grow.

I have tried multiple versions of a REGEX_Replace and nested REGEX_Replace formula (IF REGEX_CountMatches([Field1], '\|')>0 THEN REGEX_Replace([Field1], ".*\|.*", "Fruit\|Fruit") ELSE "Fruit" ENDIF) but again the variable length of the lists is causing issues. Guessing I am not using the Meta Characters correctly, but am not experienced enough to utilizes them in a way that will work.

I appreciate the help and thoughts on how to solve. Thank you!

1st 2 columns is data set with 3rd being desired results:

not correct, but the best I have gotten so for:

Regex

Accepted answers

jrlindem

I'm not totally sure i'm interpreting your need correctly, but I was able to get a result (albeit kind of a weird approach) that matches what I believe your expected output to be. Workflow attached as well.

I used a Text input tool to map the fruits to the word "fruit" but I think you could also use a Multi-Field Formula tool to convert any found text across the transposed fields into the word "fruit" if you wanted to. Little more advanced option though, so if you know the list of fruits that will show up, just map a crosswalk table and append the values as I've illustrated.

Hope that helps, -Jay

Fruit Workflow.yxmd

LJGatlin

I also found this relatively straight forward solution. Testing now to see how well it works with all of my categories.

All comments

jrlindem

Hope that helps, -Jay

Fruit Workflow.yxmd

LJGatlin

Thank you jrlindem. The approach you have laid out is another of my backup plans, thank you! Within the larger workflow, fruits is just one of many categories I would need to do this for (Vegetables, Grains, Meats....), things would get a bit cumbersome with this approach. Hoping to utilize a formula that can be replicated for each of the categories, but do realize I may need to use something what you have laid out instead. Thank you!

jrlindem

@LJGatlin Ya, I kind of assumed the real use-case was more complex. You're going to have to have some sort of a reference list though to flag each value as fruit, vegetable, or other category. I can't imagine brute-forcing that into a formula is the right approach. But then again, a managed list requires maintenance.

Does your data include a flag to identify what category the values are? If so, i'd recommend building something off the combination of the two fields.

If you want to provide a fuller example set, i'm happy to keep experimenting with you. -Jay

LJGatlin

I also found this relatively straight forward solution. Testing now to see how well it works with all of my categories.

ridolfo

I was able to do this using the RegEx tool with a replace function

Basically the formula was simply:

[^|]+
which replaces anything that is not the delimiter with "fruit"

If you wanted to use a formula:
REGEX_Replace([Field1], "[^|]+", "Fruit")

I agree that if you wanted to do this for multiple items, i.e. grains, vegetables, etc you would need to flag them and build in more details but this could be a start...

WF Fruit.png

jrlindem

@LJGatlin & @ridolfo -

I agree that those regex statements can replace the string with "fruit" however, neither account for the variable requirement to handle fruits vs vegetables vs meats when they're all in the same list. That said, if the solutions works, great! But for others visiting this thread, keep this differentiation in mind.

-Jay

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703