Column with hierarchy data that needs to be split into separate columns

[EricaH]

I have a data set where one column includes several levels of data that I need to split out into separate columns with the higher levels repeating as the lower levels are split out.  For instance, General is the highest so it should repeat all the way down for all columns flowing right until it gets to the next highest level.  I'm having difficulty knowing where to start to get these columns split out in Alteryx and any assistance starting would be greatly appreciated.

[MarqueeCrew · Accepted solution]

@EricaH ,

I used a series of formula tools (formula plus multi-row formula) to achieve your result.  The hierarchy is set by 4 spaces.  You could replace "    " with "|" and get to a similar result via a text to columns tool.  Then I checked to see if the prior hierarchy is the same and if the value for a lower hierarchy is empty, then i accept the value above it.

A picture is easier...

A workflow is easier...

Cheers,

Mark

[JoBen]

Hi @EricaH, here is a solution that I came up with using the RegEx tool and Text to Columns tool. Hope this helps. 

[EricaH]

Thank you for your help!