Matching Text between Brackets in Consecutive Records

[alexg1]

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	1
Robert	0
Robert	0

Any assistance is appreciated.

[CharlieS · Accepted solution]

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

[alexg1]

Thank you! This was a big help!