Multi-Row Formula REGEX_Match not working

[nxh038]

IF REGEX_Match([Row-1:Value], "\s\d{4}$") THEN 0 ELSE [Value] ENDIF

I'm trying to match the first row that contains a space followed by a year (i.e. " 2021") and then replace all the rows after that row with 0. Using the expression above, there is no effect on my data. Is the expression being used incorrect? I've tried the regex pattern on its own elsewhere and it is matching perfectly.

[atcodedog05]

Hi @nxh038 

Can you provide sample data and expected output.

[Sebastiaandb]

Have you checked your input? For example, i couldn't add trailing spaces in the text input, it trimmed them out so i had to manually add them again with the formula tool :-). Maybe my example helps. 

[nxh038]

Hi @atcodedog05

Attached is the sample data. I want to replace all the cells after the row "6 August 2021" with 0 so I can then delete them.

So the expected output would be records 1-6 as they are, and records 7-10 all replaced with 0.

[atcodedog05]

Hi @nxh038 

RegexMatch should be

REGEX_Match([Row-1:Value], ".*\s\d{4}$") 

.* added before try it out

[nxh038]

It replaced record 7 with 0 but not 8-10

[atcodedog05 · Accepted solution]

Hi @nxh038 

Please find the formula

IF REGEX_Match([Row-1:Value],".*\s\d{4}$") 
THEN 0 
elseif 
[Row-1:Value] = '0'
then
'0'
ELSE [Value] 
ENDIF

Workflow:

Hope this helps : )

[Sebastiaandb]

IF REGEX_Match([Row-1:VALUE],"\d{1}\s\w+\s\d{4}$")
THEN 0
elseif
[Row-1:VALUE] = '0'
then
0
ELSE [VALUE]
ENDIF

[nxh038]

Perfect! Thank you!

[atcodedog05]

Happy to help : ) @nxh038 

Cheers and have a nice day!