Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here
SOLVED

Help with Parsing

Highlighted
5 - Atom

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! 

Highlighted
5 - Atom

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. 

Highlighted
9 - Comet

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

Highlighted
16 - Nebula
16 - Nebula

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.

Labels