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! 

[ShankerV]

Hi @madhurinani 

We can achieve the solution in 2 ways.

1. Using Regex tool.

2. Using Text to Columns tool and using formula to find and replace.

Many thanks

Shanker V

[Luke_C]

Hi @madhurinani 

Here's one way with the text to columns tools:

[ShankerV]

Hi @madhurinani 

solution 2:

Input with colon and without colon

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[Felipe_Ribeir0]

Hi @madhurinani 

One way of doing this

[madhurinani]

@Luke_C : I might not have all the column headers with colon (:)

Example:

I modified the example as below. Removed the column from brand kia

And once I run the workflow, somehow the data is incorrectly formatted as below

Do you know how can this issue be fixed?

Thanks

[madhurinani]

@ShankerV : Based on my understanding, the workflow is not dynamic. 

If the i/p file has an additional column, then I would have to manually Text Input and the column header name as well for the workflow to get all the column header populated in the o/p file

Do you know how I can fix that issue?

Thanks!

[ShankerV]

Hi @madhurinani 

Yes, we can make it dynamic. 

[ShankerV]

@madhurinani 

Please find the dynamic way to reach the solution.

Input with and without colon

Note: The workflow used to achieve the solution is attached which can be downloaded to see how the solution works.

If you believe your problem has been resolved. Please mark helpful answers as a solution so that future users with the same problem can find them more easily!!!!

Many thanks

Shanker V

[binuacs]

@madhurinani One way of doing this