How to find substring of one field in another field?

[marlline]

Hi Everyone,

I have a question, how to find part of one character field in another character field.

Following is the example. I tried to find the contacts that have purchased something additional in later purchase i.e. something they did not buy during 1st purchase.

I used

Match(right left)=FindString([1st Purchase], [Later Purchase]) and

Match(left right)=FindString([Later Purchase], [1st Purchase]).

I found that when Match(left right) = -1, it seems to give me what I wanted. But it cannot differentiate when part of Later Purchase is in 1st Purchase. Please kindly help with your solutions. Thank you so much!

1st Purchase	Later Purchase	Match (right left)	Match (left right)	Flag
Banana,Juice,Kiwi	Banana,Kiwi	-1	-1	Wrong
Banana,Juice,Kiwi	Banana,Juice,Volume Fruit	-1	-1	Fine
Banana,Juice,Kiwi	Banana	-1	-1	Wrong
Banana,Juice,Kiwi	Juice,Kiwi	-1	-1	Wrong
Banana,Juice,Kiwi	Banana,Juice	-1	-1	Wrong
Banana,Juice,Kiwi	Starfruit	-1	-1	Fine

[MarqueeCrew]

Hi @marlline,

I'm caught up in something now, but here is a quick workflow to get you started.

1. Assign RecordID
2. Parse to Rows (1st purchase items)
3. Parse to Rows (2nd purchase items)
4. Lookup (Find & Replace) if 2nd item is in 1st list - no match = Null()
5. Formula to assign False or True to match
6. Summarize to find WORST match for a record
7. Each RecordID contains a match flag

Cheers,

Mark

[DanM · Accepted solution]

@marlline The reason why you are having the issue is that it is taking the entire string attempting to match it. If you parse the Later Purchase to rows and then use a contains statement in the Formula tool, you will see that the expression will designate if the Later Purchase is located in the 1st Purchase. You can then move forward with your next step.  Attached workflow 

[marlline]

I tried it and it works perfectly!!!

THANK YOU SO MUCH @DanM!

[JohnDanahy]

Brilliant solution, thank you!