Regex(?) formula to remove values matching the ones stored in other field

[kwieto]

Check two field, each containing multiple values, separated by "|" sign, against each other and list differences for each field.

So basically if field 1 has values like ABC|DEF|GHI and Field 2 has DEF|JKL I want to have two outputs:
For Field 1 - ABC|GHI (values which are not available in Field 2)
For Field 2 - JKL (value not available in Field 1)

I know that I can split both fields into rows, then use join to match same values and have differences listed in L and R output, but just for the sake of playing I am trying to do the same thing via formula. 
I created one which is basically Regex_Replace([Field 1], [Field 2], "") which, since the "|" sign stands for OR in RegEx, basically works. - in the above case it will translate to Regex_Replace("ABC|DEF|GHI", "DEF|JKL", "") and return ABC||GHI (it requires cleaning unnecessary "|" signs afterwards, but that's not a problem)

The problem is when we have two similar values, like "Name" and "First Name" in that case, if one field will contain "First Name" and the second one just "Name", the end result will be "First " instead of "First Name".

Any Ideas how to modify the formula to properly cover such cases as well?

[caltang]

Like so?

[kwieto]

Unfortunately not. It works only if each value is different, but not if they are overlapping:

[kwieto]

Interestingly, it will work if I first replace all spaces with other symbol, i.e. underscore ("_")
I tried that trick already, but without marking "\b" as you proposed, it didn't work

[caltang]

Hmm @kwieto  what is the expected output then? Can you give a few scenarios?

[kwieto]

OK, so the ultimate output should be like that:

Field 1	Field 2	Field 1 != Field 2	Field 2 != Field 1
Apple|Banana|Orange	Apple|Banana|Lemon	Orange	Lemon
Apple|Red Apple|Apple Small|Orange	Apple|Banana	Red Apple|Apple Small|Orange	Banana
Apple|Green Apple|More Green Apple|Even More Green Apple	More Green Apple|Less Green Apple	Apple|Green Apple|Even More Green Apple	Less Green Apple
Banana|Banana Rotten|Banana Rotten Extremely	Banana|Orange	Banana Rotten|Banana Rotten Extremely	Orange

[caltang]

I’ve tried a few ways but I wasn’t able to crack it (yet). The problem is if it exists and is not fully parsed in the 3rd and 4th columns… may need a few steps to get desired output rather than a direct regex I fear…

[Qiu]

@kwieto @caltang 
Lets stop playing and use the Text to Column tool 😂

[flying008 · Accepted solution]

Hi, @kwieto 

As you want:

Spoiler

Replace(REGEX_Replace(Replace([Field 1],' ', '_'), '(\b' + Replace(Replace([Field 2],' ', '_'), '|', '(?:[^_])|\b') + '(?:[^_]))', ''), '_', ' ')

Field 1	Field 2	Field 1 != Field 2	Field 2 != Field 1
Apple|Banana|Orange	Apple|Banana|Lemon	Orange	Lemon
Apple|Red Apple|Apple Small|Orange	Apple|Banana	Red Apple|Apple Small|Orange	Banana
Apple|Green Apple|More Green Apple|Even More Green Apple	More Green Apple|Less Green Apple	Apple|Green Apple|Even More Green Apple	Less Green Apple
Banana|Banana Rotten|Banana Rotten Extremely	Banana|Orange	Banana Rotten|Banana Rotten Extremely	Orange

[caltang]

Super cool!! Learned a new trick :)