Parsing numbers from strings

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

Regex

Parse

Accepted answers

cmcclellan

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.

cgoodman

@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

binu_acs

@Misslady are you looking something like below?

Parsing.yxmd

All comments

cgoodman

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.

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,

email sample.xlsx

cgoodman

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

binu_acs

@Misslady are you looking something like below?

Parsing.yxmd

Misslady

Thank you, this solution is perfect

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703