Parsing comma from String and numbers

[danielmorgan1]

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? 

[alexnajm]

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

[danielmorgan1]

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),

[alexnajm · Accepted solution]

Exactly - this should work well then: 

[danielmorgan1]

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