Get number from string formula regex

[RichardAlt]

Hi all,

I'm having problem in my workflow, How can I extract the numbers from string using regex.

This is my sample string:

Total : 11,274.75 762,350.34 0.00 No of Policies 0 1

I'm using this IIF Statement:

IIF(Contains([New Field], "Total :"), REGEX_Replace([New Field], "[\d,]", ""), [New Field])

The output is this:  

Total : . . . No of Policies

But when I use this IIF Statement:

IIF(Contains([New Field], "Total :"), REGEX_Replace([New Field], "[\D,]", ""), [New Field])

My output is this:

11274757623503400001

The result is number only.

But my desired output is something like this:

11,274.75 762,350.34 0.00 No of Policies 0 1

Just remove the Total : word.

[grazitti_sapna · Accepted solution]

Hi @RichardAlt , instead of using regex_replace have you tried using only replace function?

Formula:

IIF(Contains([Field1], "Total :"), Replace([Field1], "Total :", ""), [Field1])

using this formula I got the desired output.

Thanks.

[christine_assaad]

Hi @RichardAlt ,

could you try to put in your replace formula the following regex syntax?

\w+\s\:

Hope this will help.

[grazitti_sapna]

@RichardAlt , if you want to solve this using the regex replace function another solution is as follows:

Formula:

IIF(Contains([Field1], "Total :"), REGEX_Replace([Field1], "\w*\s:\s", ""), [Field1])

Thanks.