Help with Parsing

[NateN]

Hello,

I have a column that I am having difficulty parsing. I have attached an excel file to better demonstrate the issue. I would like the parse column A into three columns as shown in columns B, C, and D. I believe the parse function of the RegEx tool would work well for this task, but I am having difficulties mastering the tool. Does anyone know of a simpler method to accomplish the task? Any and all help would be greatly appreciated! 

[NateN]

Sorry, I attached the old version of the excel file to the original post. Please refer to the excel file in this comment for the correct file. 

[brindhan]

Hi @NateN

Here's your solution. Hope this helps.

Tool- regex

Output method- Parse

Expression-   ([^(?:total)].+)\((\<\w+\>)\s\$(.+)\)

PS: You will have to use a data cleansing tool after this to remove unwanted spaces from the above output.

Cheers,

Brindha

[jdunkerley79 · Accepted solution]

As always with Alteryx there are many ways to do this.

I would use a formula tool with expression:

ToNumber(Replace(REGEX_Replace([F1],"^.*\$([0-9,]+).*?$", "$1"),",",""))

This does 3 steps:

1. Pick out the number part - REGEX_Replace([F1],"^.*\$([0-9,]+).*?$", "$1")

2. Remove the Commas - Replace( ... ,",",""))

3. Convert to a number

Sample attached.