Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.
SOLVED

Finding string value and extract dollar value

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