tax codes with comma separator

[Cat_H]

Hi I'm new to Alteryx and need help. 

I have a single excel column/cell that contains tax information "Tax String", each row represents a product. The tax information format will be the same, each tax will appear as "TaxCode(Space)ValueWithDecimals(Comma)" - as per the below example.

The taxes will vary by product type. How can i take the "tax string" and convert it to organized individual tax code columns (see red). 

Product Type	Tax string	YQ	CA	SQ	RC	DE	RA	UP	FN	EX	HB
Product 1	YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, UP 18.75	1120.00	25.91	25.00	3.25	14.45	31.02	18.75
Product 2	YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, FN 18.75	1120.00	25.91	25.00	3.25	14.45	31.02		18.75
Product 3	YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, EX 18.75	1120.00	25.91	25.00	3.25	14.45	31.02			18.75
Product 4	CA 25.91,SQ 25.00,RC 3.25,DE 14.45,HB 31.02, UP 18.75		25.91	25.00	3.25	14.45	31.02	18.75			31.02

Any help would be greatly appreciated!

Thanks!
Cat

[markcurry · Accepted solution]

Hi Cat, see attached, there's one way of doing it...

[deviseetharaman · Accepted solution]

Hi @Cat_H 

Is this what you are looking for

[Cat_H]

Thank you so much! I've been stumped all day! Thank you!!!

[Cat_H]

Hi everyone. I need your help once again. It's the same scenario, i want to break up the taxes only in the same way described in my original message only the data i received was a lot more different then what i was expecting. 

What i was expecting:

YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, UP 18.75

YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, UP 18.75

YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, UP 18.75

What i got:

Code,Currency,Amount,Status XG,CAD,22.70,TO BE PAID RC,CAD,3.25,TO BE PAID XQ,CAD,45.28,TO BE PAID SQ,CAD,55.00,TO BE PAID Total,CAD,126.23,TO BE PAID

Code,Currency,Amount,Status CA,CAD,14.25,TO BE PAID XG,CAD,14.56,TO BE PAID RC,CAD,3.25,TO BE PAID XQ,CAD,29.05,TO BE PAID SQ,CAD,55.00,TO BE PAID Total,CAD,116.11,TO BE PAID

Code,Currency,Amount,Status CA,CAD,14.25,TO BE PAID XG,CAD,14.56,TO BE PAID RC,CAD,3.25,TO BE PAID XQ,CAD,29.05,TO BE PAID SQ,CAD,55.00,TO BE PAID Total,CAD,116.11,TO BE PAID

Do you have any advise on how to extract the taxes/code from this?

[jeff_reynolds]

Try the attached workflow. It gets you most of the way there. The one issue I see is that there is no delimiter between the Status, and the next section of data. To get around that, I hard-coded one in via the formula tool, but that will break on any status other than 'TO BE PAID'. I'm sure something could be done via RegEx, but this will hopefully get you pointed in right direction.

Good luck. 

[Cat_H]

Thank you Jeff 🙂 I'll try and add it in to day and see if i can tweak it.