Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Finding string value and extract dollar value

oscarlim
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
9 REPLIES 9
MarqueeCrew
20 - Arcturus
20 - Arcturus

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 restart. Order shall return.
Please Subscribe to my youTube channel.
NicoleJohnson
ACE Emeritus
ACE Emeritus

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
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

oscarlim
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

oscarlim
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

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
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 restart. Order shall return.
Please Subscribe to my youTube channel.
oscarlim
6 - Meteoroid

Hi Mark,

Thanks for the Replace tip.  It did the trick.

Regards,

Oscar

hellyars
13 - Pulsar

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

MarqueeCrew
20 - Arcturus
20 - Arcturus
\$

Slash followed by a character is the literal value.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
Labels