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 TypeTax stringYQCASQRCDERAUPFNEXHBProduct 1YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, UP 18.751120.0025.9125.003.2514.4531.0218.75 Product 2YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, FN 18.751120.0025.9125.003.2514.4531.02 18.75 Product 3YQ 1120.00,CA 25.91,SQ 25.00,RC 3.25,DE 14.45,RA 31.02, EX 18.751120.0025.9125.003.2514.4531.02 18.75 Product 4CA 25.91,SQ 25.00,RC 3.25,DE 14.45,HB 31.02, UP 18.75 25.9125.003.2514.4531.0218.75 31.02 Any help would be greatly appreciated! Thanks! Cat

tax codes with comma separator

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

Accepted answers

markcurry

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

Split Tax Codes.yxmd

deviseetharaman

Hi @Cat_H

Is this what you are looking for

Tax.yxmd

All comments

markcurry

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

Split Tax Codes.yxmd

deviseetharaman

Hi @Cat_H

Is this what you are looking for

Tax.yxmd

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

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

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.

taxStatus.yxmd

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235