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

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?

Regex

Tips and Tricks

Accepted answers

flying008

Hi, @kwieto

As you want:

Spoiler

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

录制_2025_04_25_08_57_43_31.gif

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

All comments

caltang

Like so?

For kwieto.yxmd

kwieto

Unfortunately not. It works only if each value is different, but not if they are overlapping: Annotation 2025-04-24 153330.png

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

Annotation 2025-04-24 154044.png

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

Hi, @kwieto

As you want:

Spoiler

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

录制_2025_04_25_08_57_43_31.gif

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

kwieto

@Qiu that would be too easy 😅
In general I use text to column by adding identifier, then splitting both columns separately with text to column tool (but using split too rows option) and joining both streams, but I am really curious if it can be achieved via formula

@flying008 - great solution!
I crafted something similar by myself, using ReplaceChar as support:

ReplaceChar(Regex_Replace(ReplaceChar([Field 1], " ", "_"),"\b(" + ReplaceChar([Field 2], " ", "_") + ")\b",""), "_", " ")

Both formulas give same output, but there is one tweak - if the value has a special character at the end or the beginning (i.e. parenthesis) It will not work.
So for example if the value like "Banana Rotten Extremely" would be "Banana Rotten (Extremely)" it won't match anymore.

One thing is that Regex consider parenthesis as special sign, so the pattern string must be modified to "Banana Rotten \(Extremely\), but I see also that the special character at the end disturbs the "\b" operator.
Probably i can wrap another Replace/Regex_Replace function to change parenthesis to some other character, but it starts to be an overkill 😂
But for simple texts it works quite efficient

flying008

Hi, @kwieto

Any pattern can be addressed with regular expressions ! 😁

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703