Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Finding string value and extract dollar value

Highlighted
6 - Meteoroid

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

 

 

 

StringOutput Column
**APPROVED NTE 773.14 per Manager** 

773.14

NTE $800.00 CS. /// Please make800.00
Maintenance request0
Please fix equipment. NTE $ 875.00875.00
**APPROVED NTE $688.22 per staff688.22
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
14 - Magnetar
14 - Magnetar

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! :)

 

 

Highlighted
6 - Meteoroid

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

Highlighted
6 - Meteoroid

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

Highlighted
6 - Meteoroid

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

 

Any help would be much appreciated. 

Thank you.

Oscar

 

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
Replace([field],",",'')

That would remove all commas from the field prior to you using your regex.
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
6 - Meteoroid

Hi Mark,

Thanks for the Replace tip.  It did the trick.

Regards,

Oscar

Highlighted
10 - Fireball

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

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
\$

Slash followed by a character is the literal value.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels