topic Parsing comma from String and numbers in Alteryx Designer Desktop Discussions

Parsing comma from String and numbers

danielmorgan1 — Wed, 14 Feb 2024 14:52:25 GMT

Hello,

I have data sets similar to the following:

States	Total Cost	Values
Texas	2,100	Food - Breakfast 500, Food - Dinner 1,200, Income (1,500), Rent - 1bdr apartment 1,800;

I am attempting to ensure the Values match up with the Total Cost. The end result would look something like the following.

State	Total Cost	Category	Value
Texas	2,1000	Food	1,700
		Rent	1,800
		Income	-1,500

Because of the comma in the numbers, it was difficult to utilize delimiters to separate the data. I need to keep the category (food, rent) before the dash. Is there a formula or tool that I can use to get rid of the comma in between numbers?

Re: Parsing comma from String and numbers

alexnajm — Wed, 14 Feb 2024 14:54:32 GMT

This looks like a Text to Columns tool, split into rows!

Re: Parsing comma from String and numbers

danielmorgan1 — Wed, 14 Feb 2024 15:00:55 GMT

Text to columns doesn't work, due to a comma being in the number and outside of the number. I think I need to clean the data first, but I couldn't figure out how to use the replace function to capture this because the comma is either after the number (string in the data set) i.e: Dinner 1,200, or Parenthesis Income (1,500),

Re: Parsing comma from String and numbers

alexnajm — Wed, 14 Feb 2024 15:04:49 GMT

Exactly - this should work well then:

Re: Parsing comma from String and numbers

danielmorgan1 — Wed, 14 Feb 2024 15:08:02 GMT

Thank you, the formula that you provided before the text to columns was exactly what I needed. Greatly appreciate the quick response and help!