Regex Replace used in formula tool

yvkpatel

I am trying to replace below strings using Regex:

1) interest payable pursuant to this Section 3(d)(ii) on an Installment Due Date will be $108,452.04.

2) "Fixed Interest Rate" means the annual interest rate of 4.36%.

Desired result: $108,452.04 #for 1st line

4.36% #for 2nd line

I am using the following methods for the respective lines:

if REGEX_Match([Field],".*fixed interest rate.*") then REGEX_Replace([Field],"(.*?)(\d+.*%)","$1.$2%")

if REGEX_Match([Field], ".*interest payable pursuant.*") then REGEX_Replace([Field], "(.*?)(\$\d+.*?)(,\d+.*)", "$1")

The result that I get using the above codes are the same previous lines, there is no change.

I don't know what I am doing wrong. Can anyone please correct my code?

Workflow

Regex

Output

Accepted answers

SamDesk

Hi @yvkpatel

What is probably happening then is that one of your previous conditions is returning true and so it never gets to your later elseif statement. You may need to tighten your previous conditions so they don't return true where you don't want them to.

You could split your current formula into separate fields and then you can see which conditions trigger to diagnose which statement is problematic.

Sam 🙂

estherb47

Hi @yvkpatel

When testing your new Regex_Replace on just that string of characters, it works. So, my thought is that within the IF/THEN/ELSEIF, that particular line is matching first to another Regex_Match in a previous ELSEIF.

If you change the order of the IFs and ELSEIFs (maybe try putting the latest one first), does it work?

Cheers,

Esther

All comments

estherb47

Hi,

You’re so close!! Within then regex replace, the what to replace with part of the formula should $2 not $1. Each parenthetical is given a number, from 1 to n. You don’t need to include all of them in the result.

First formul should return $2, and second should return $3.

Let me know if that helps!

Cheers,
Esther

Thableaus

Hi @yvkpatel

Would this work?

IF Contains([Field1], "Fixed Interest Rate")
THEN
REGEX_Replace([Field1], ".*?(\d+\.?\d+%).*", "$1")
ELSEIF Contains([Field1], "interest payable pursuant") THEN
REGEX_Replace([Field1], ".*?(\$.*\b).*", "$1")
ELSE [Field1] ENDIF

Cheers,

yvkpatel

Thank you very much for helping me out learn and providing me a solution.

I got the correct output now.

estherb47

Wonderful!! Glad it’s working for you now.

--
Esther Bezborodko
*Senior Manager*
201.650.7314 | estherbezborodko@gmail.com
beautycounter.com/estherbezborodko

*Our mission is to get safe products in the hands of everyone.*
[image: Facebook]

yvkpatel

Hello there,

Now I am trying the same thing with the statement:

an actual/360 interest calculation schedule (interest is payable for the actual number of days in each month, and each month's interest is calculated by multiplying the unpaid principal amount of this Note as of the first day of the month. Each monthly payment of principal and interest will first be applied

with the code:

if REGEX_Match([Field],".*interest calculation schedule.*") then REGEX_Replace([Field],"(.*?)(\w+\/\w+)(.*)","$2")

I also tried the code:

if REGEX_Match([Field],".*interest calculation schedule.*") then REGEX_Replace([Field],".*?(\w+\/\w+).*","$1")

to get the output as:

actual/360

Still not getting the proper output from either of the codes.

Can anyone please help me out here if I am still missing out something which I haven't encountered before?

SamDesk

Hi @yvkpatel

Just wanted to check that your code does include the rest of the IF statement and not just what you shared, otherwise you'll be getting syntax errors. So it should in full look something like the below:

IF
REGEX_Match([Field],".*interest calculation schedule.*") 
THEN
REGEX_Replace([Field],".*?(\w+\/\w+).*","$1")
ELSE
Null()
ENDIF

I have tried to run this with your sample statement and get the desired output of "actual/360".

Sam 🙂

yvkpatel

Hi,
I have used this along with other 'IF' statements using 'ELSEIF'.

So is there any problem with using more elseif statements? The above statement was in the 3rd elseif condition.

SamDesk

Hi @yvkpatel

You could split your current formula into separate fields and then you can see which conditions trigger to diagnose which statement is problematic.

Sam 🙂

estherb47

Hi @yvkpatel

If you change the order of the IFs and ELSEIFs (maybe try putting the latest one first), does it work?

Cheers,

Esther

yvkpatel

I tried changing the order of else if conditional statements and by putting the last one 1st I got the correct answer. So I think as you all said above regarding the problem I was facing it was totally true and Thank You for your kind support with the whole problem.

Regards,

Yaksh Patel

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703