How to Trim the string data and paste in different columns based on a specific rule?

[madhurinani]

Hello All,

I have the data in below format:

Column 1	Column 2
test 1	Name: abc, brand: kia, type: EV, color: blue
test 2	Name: xyz, brand: bmw, type: EV, color: white
test 3	Name: qwe, brand:audi, type:gas, color: red

I would like the output as follows:

Column 1	Name	brand	type	color
test 1	abc	kia	EV	blue

Some of the column headers might not have the colon(:) and might just be "Name abc". For this I think I might need to create a specific rule for that.

Is that something that can be achieved in Alteryx?

Any help would be appreciated.

Thanks! 

[binuacs]

@madhurinani Updated the workflow based on your new input

[madhurinani]

Hello @binuacs ,

Thank you for your workflow.

If suppose the Number column has dashes(-) between the numbers. How will that work?

Example: 123-456-789 instead of 123456789

I think there needs some tweaking on the regex formula you added, right?

Thanks!

[binuacs]

@madhurinani you need to use the ReplaceChar() function to replace the _ to ''