Finding string value and extract dollar value

oscarlim

I would like to be able to extract the dollar value inside a string.  

The first condition is to find "NTE" only in the string.  i.e. should not pick-up words like "maintenance" 

From the strings that contains "NTE", capture or extract the dollar value into a new column

How do I do this in Alteryx?

Thank you for your help.

Regards,

Oscar

String	Output Column
**APPROVED NTE 773.14 per Manager**	773.14
NTE $800.00 CS. /// Please make	800.00
Maintenance request	0
Please fix equipment. NTE $ 875.00	875.00
**APPROVED NTE $688.22 per staff	688.22

MarqueeCrew

Hi @oscarlim,

Here's a formula to try:

It has 4 patterns that it searches for after finding NTE.

ToNumber(
REGEX_Replace([String], ".*\bNTE\s.*?(\d+\s|\d+\.\d+\s|\d+$|\d+\.\d+$).*", '$1')
)

The description from regex101.com is:

.*\bNTE\s.*?(\d+\s|\d+\.\d+\s|\d+$|\d+\.\d+$).*

.*   matches any character (except for line terminators)

* Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy)

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

NTE matches the characters NTE literally (case sensitive)

\s matches any whitespace character (equal to [\r\n\t\f\v ])

.*?

 matches any character (except for line terminators)

*? Quantifier — Matches between zero and unlimited times, as few times as possible, expanding as needed (lazy)

1st Capturing Group 

(\d+\s|\d+\.\d+\s|\d+$|\d+\.\d+$)

1st Alternative 

\d+\s

\d+

 matches a digit (equal to [0-9])

+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

\s matches any whitespace character (equal to [\r\n\t\f\v ])

2nd Alternative 

\d+\.\d+\s

\d+

 matches a digit (equal to [0-9])

+ Quantifier — Matches between one and unlimited times, as many times as possible, giving back as needed (greedy)

\. matches the character . literally (case sensitive)

\d+

 matches a digit (equal to [0-9])

\s matches any whitespace character (equal to [\r\n\t\f\v ])

3rd Alternative 

\d+$

\d+

 matches a digit (equal to [0-9])

$ asserts position at the end of the string, or before the line terminator right at the end of the string (if any)

4th Alternative 

\d+\.\d+$

.*

 matches any character (except for line terminators)

* Quantifier — Matches between zero and unlimited times, as many times as possible, giving back as needed (greedy)

Global pattern flags

g modifier: global. All matches (don't return after first match)

Cheers,

Mark