Calling all Racers for the Alteryx Grand Prix! It's time to rev your engines and race to the stage at Inspire! Sign up here.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

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

6 - Meteoroid

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.

20 - Arcturus
20 - Arcturus

@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...





Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
11 - Bolide

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



6 - Meteoroid

Thank you for your help!