Text to column/Parsing Question

[znooredeen004]

Hi All,

See the sample excel file that I uploaded.

Essentially I would like to separate out the data in the State, Income and Tabs by the delimiter | however this seems complicated with corresponding columns to take into account.

For example using John it should be

Name                 State                Income         Tax

John                   Alabama          4000              250

John                   Arizona            5000              350

John                   New York         2000              150

John                   Georgia           10000             600

Is Alteryx capable of something like that?

If not Alteryx is there another application I could use?

[CharlieS · Accepted solution]

Oh yeah, Alteryx can do that.

I will offer a word of caution that this will depend on the the same number of elements being available in each field. Otherwise, the position-based transformation breaks down without other rules in place. 

Check out the attached workflow to see this in action.

[znooredeen004]

Thank you!

[MarqueeCrew · Accepted solution]

@znooredeen004 ,

An ALTERNATIVE solution to the one that my friend, @CharlieS provided is attached.  It is dynamic and will perform this task on an unlimited set of fields.  Like Charlie's solution, it does assume that the number of values per field match.  But, I think that it will still work.  Maybe it's worth a test and possible another solve that I can carve into my laptop.

Cheers,

Mark

[znooredeen004]

@marqueecrew

I tried this method and it worked as well. Although it was a bit longer, it seems like it will work over a greater number of rows. Thanks as well!