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.
Go to Solution.
Go to Solution.
Here's a formula to try:
It has 4 patterns that it searches for after finding NTE.
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*.*$
Output Column = RegEx_Replace([String],"^.*NTE \$*\s*(\d+\.*\d*)\s*.*$","$1")
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 ?
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?
I still can't figure out how to parse the $ value with commas and zeroes.
Any help would be much appreciated.
Thanks for the Replace tip. It did the trick.
@MarqueeCrew how do you account for the string dollar sign $?