This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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 |
Solved! Go to 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:
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! 🙂
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
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
I still can't figure out how to parse the $ value with commas and zeroes.
Any help would be much appreciated.
Thank you.
Oscar
Hi Mark,
Thanks for the Replace tip. It did the trick.
Regards,
Oscar
@MarqueeCrew how do you account for the string dollar sign $?