Regex(?) formula to remove values matching the ones stored in other field
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
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?
Solved! Go to Solution.
- Labels:
- Regex
- Tips and Tricks
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
@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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator

- « Previous
-
- 1
- 2
- Next »