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 · Accepted solution]

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

[NicoleJohnson]

I believe the following RegEx string will work, either using Parse method in the RegEx tool, or as part of a RegEx_Replace formula in a Formula tool:

RegEx tool Parse method:

Regular Expression = ^.*NTE \$*\s*(\d+\.*\d*)\s*.*$

Formula method:

Output Column = RegEx_Replace([String],"^.*NTE \$*\s*(\d+\.*\d*)\s*.*$","$1")

NJ

PS. Which is just a different method of doing the same thing as @MarqueeCrew solution! :)

[oscarlim]

Thanks Nicole and MarqueeCrew.  

I failed to mention that some of the dollar values have commas.  i.e. 4,232.12

When I tried the RegEx tool Parse method, the return value is a 4

Where in the syntax shall I include the parsing of the comma so that it returns 4232.12 ?

Thanks again.

Oscar

[oscarlim]

Hi Mark, 

I tried the formula and it worked.  

However, I had one result where it returned a 70.

The string is "NTE $ 2,070.00 Approved"

How can this be remedied please?  

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

 Thank you.

Oscar

[oscarlim]

I still can't figure out how to parse the $ value with commas and zeroes. 

Any help would be much appreciated. 

Thank you.

Oscar

[MarqueeCrew]

Replace([field],",",'')

That would remove all commas from the field prior to you using your regex.

[oscarlim]

Hi Mark,

Thanks for the Replace tip.  It did the trick.

Regards,

Oscar

[hellyars]

@MarqueeCrew  how do you account for the string dollar sign $?

[MarqueeCrew]

\$

Slash followed by a character is the literal value.

Cheers,

Mark