Matching Text between Brackets in Consecutive Records

Question

Hello all,

I am trying to determine if two consecutive records in a table match. They may be variable length, and they may differ, BUT the ones I need to match start with bracketed text and everything between those brackets should be standardized. For example my records would look like this:

[John] Snow
[Tyrion] Smith
[Tyrion] Lannister
[Ned] Jones
[Ned] Stark
Robert

Robert

I tried using the following RegEx, but I kept getting a malformed function call:

IF REGEX_Match([Row-1:NAME], $$.*?$$) = REGEX_Match([NAME], $$.*?$$)

THEN 1

ELSE 0

ENDIF

The result I am trying to produce would look like this:

[John] Snow

0[Tyrion] Smith

0[Tyrion] Lannister

1[Ned] Jones

0[Ned] Stark

1Robert

0Robert0

Any assistance is appreciated.

CharlieS · Accepted Answer

First and foremost, great example data. In your posted expression, don't forget that the RegEx expressions need to be in quotes when using that function:

REGEX_Match([Row-1:NAME],"$$.*?$$")

Otherwise, I was able to achieve the desired output using substring( functions. Here's the formula I used:

IF findstring([NAME],"[")!=-1 AND
substring([NAME],findstring([NAME],"[")+1,findstring([NAME],"]")-1)==substring([Row-1:NAME],findstring([Row-1:NAME],"[")+1,findstring([Row-1:NAME],"]")-1)
THEN 1
ELSE 0 ENDIF