Parsing numbers from strings

[Misslady]

I'm trying to extract the numbers that follow specific strings in a field. 
from the example below, I'm trying to extract the amounts that follow Debit total, EBT Total, eWic Total, Pay Card total, Money Network Total and Grand Total. The data doesnt always have all these categories (sometimes ewic, pay card and/or money network might be missing). I know this can be accomplished with Regex but i'm not sure how. 

Amount   Debit Total $185,766,852.45   EBT Total $42,450,099.98   eWIC Total
$1,337,679.43   Pay Card Total -$503,352.84   Money Network Total -$45,168.63  

--------------------------------------------------------------------------------

Grand Total $229,006,110.39  

[cgoodman3]

Is there a rule on what will occur and the order? The regex ([0-9,.]+) on tokenise will split all matches of numbers including the thousand separator and decimals.

[Misslady]

Thanks Chris,

The issue I have here is that if one of the categories are missing (lets say) eWic, Alteryx will just more the other numbers up and it will cause my calculations and data output to be wrong

[Qiu]

@Misslady 
Can you provide a bigger data sample? so we can try something else.

[cmcclellan · Accepted solution]

So you can't use regex immediately, I'd be using a combination of FindString and SubString to "pull out" the different fields before using regex, you might find that you don't even need regex at all.

[Misslady]

@Qiu I Receive the information via email and use power query to parse it to excel then Alteryx picks up the new day from excel. Attached is a sample of what the field i'm trying to parse looks like. 

Thanks,

[cgoodman3 · Accepted solution]

@Misslady you can you a combination of @cmcclellan string functions and regex to get you want given the values are not always present. For example if you want to get the “Debit Total” use FindString() to get the starting position of the words Debit Total. Then put this inside a substring and add a length to this, and then use the regex formula to only extract the numeric bit. I’m only on a phone but it would be something like substring([Field1],findstring([Field1],”Debit Total”),20) then use regex to parse our the digits.

There’s more details for string functions here; https://help.alteryx.com/20213/designer/string-functions

[binuacs · Accepted solution]

@Misslady are you looking something like below?

[Misslady]

Thank you, this solution is perfect